Archive for September, 2009

We had someone trying to connect to a table in a database using access and linked tables. They kept getting ODBC – call failed when they tried to access the data. If a table didn’t have a primary key then it worked!
I ran a trace and saw it was calling a select statement against the Primary Key in that table. There were no errors but an attention warning appeared. I then spread the trace to all databases and go this error:
The EXECUTE permission was denied on the object ‘sp_execute’, database ‘mssqlsystemresource’, schema ‘sys’.
I had locked down this server so now had to open it up a little.
I created a role in Master called AccessUsers. I granted the role Execute on ‘sp_execute’.
Now they can connect using access.
I’ll show the lock-down scripts in another entry.

I don’t like the system generated maintenance plans so use this instead.

I normally go from one step to the next with total disregard as to it’s outcome. This is because I check job histories and logs each morning so if update stats or integrity checks failed I will know about it and fix it then. I also want my backups to run regardless – they stay on disk for 3 days but are also copied to tape so if a problem with integrity creeps in and isn’t noticed for > 3 days (I am on holiday) then if I can’t fix it I can use tape backups to repair almost as easily as disk backups. If this doesn’t suit you you could add an extra step which takes a backup but does not delete old backups – you just need the space for this if you can’t check for a while.

The most unsatisfying thing with this is detecting read only databases – it is easy in 2005 but haven’t found a satisfactory easy method for 2000.

I have a separate job for system dbs where NOT IN is replaced with IN. This runs at a different time of the evening

 

Step 1: Update Usage and Checkpoint

USE Master;

DECLARE @dbName varchar(255);

DECLARE @SQL varchar(255);

DECLARE dbCursor CURSOR Local Fast_Forward FOR

 select [name]

 From master..sysdatabases

 WHERE [Name] not in (‘master’,‘msdb’,‘tempdb’,‘model’,‘Distribution’,‘Resource’)

 Order by [name];

OPEN dbCursor

 FETCH NEXT FROM dbCursor INTO @dbName

WHILE @@FETCH_STATUS = 0

BEGIN

Set @sql = ‘dbcc updateusage ([‘ + @dbName + ‘]) WITH NO_INFOMSGS;’;

Exec (@sql);

Set @sql = ‘Use ‘ + QuoteName(@dbName) + ‘ Checkpoint;’;

Exec (@sql);

Use Master;

FETCH NEXT FROM dbCursor INTO @dbName;

END;

CLOSE dbCursor;

DEALLOCATE dbCursor;

 

Step 2: Integrity Check

USE Master;

DECLARE @dbName varchar(255);

DECLARE @SQL varchar(255);

DECLARE dbCursor CURSOR Local Fast_Forward FOR

select [name]

From master..sysdatabases

WHERE [Name] not in (‘master’,‘msdb’,‘tempdb’,‘model’,‘Distribution’,‘Resource’)

Order by [name];

OPEN dbCursor

FETCH NEXT FROM dbCursor INTO @dbName;

WHILE @@FETCH_STATUS = 0

BEGIN

Set @sql = ‘dbcc CHECKDB ([‘ + @dbName + ‘]) WITH NO_INFOMSGS;’;

Exec (@sql);

FETCH NEXT FROM dbCursor INTO @dbName;

END;

CLOSE dbCursor;

DEALLOCATE dbCursor;

 

Step 3: Update Stats

USE Master;

DECLARE @dbName varchar(255);

DECLARE dbCursor CURSOR Local Fast_Forward FOR

/* 1048 represents REadonly databases but it is no the only code that represents Readonly

you will need to check your dbs*/

select [name]

From master..sysdatabases

WHERE Name not in (‘master’,‘msdb’,‘tempdb’,‘model’,‘Distribution’,‘Resource’)

AND status <> 1048

Order by [name];

OPEN dbCursor

FETCH NEXT FROM dbCursor INTO @dbName

WHILE @@FETCH_STATUS = 0

BEGIN

Exec (‘Use [‘ + @dbName + ‘] EXEC sp_MSforeachtable ”UPDATE Statistics ? With Sample 50 Percent”’);

FETCH NEXT FROM dbCursor INTO @dbName;

END;

CLOSE dbCursor;

DEALLOCATE dbCursor;

 

Step 4; Backup databases

USE Master;

DECLARE @rootDir as Varchar(255);

SET @rootDir = ‘E:\Backup’; /*Enter path to user backups here*/

DECLARE @dbName varchar(255);

DECLARE dbCursor CURSOR Local Fast_Forward FOR

select [name] From master..sysdatabases

WHERE Name not in (‘master’,‘msdb’,‘tempdb’,‘model’,‘Distribution’,‘Resource’)

Order by [name];

OPEN dbCursor

FETCH NEXT FROM dbCursor INTO @dbName;

WHILE @@FETCH_STATUS = 0

BEGIN

DECLARE @BakCommand as varchar(1024)

SET @BakCommand = ‘master.dbo.xp_sqlmaint N”-S . -D ‘ + @dbName + ‘ -BkUpDB “‘+ @rootDir + ‘” -BkUpMedia DISK -CrBkSubDir -DelBkUps 3DAYS -BkExt “BAK””’;

–Print @BakCommand

EXEC (@BakCommand);

FETCH NEXT FROM dbCursor INTO @dbName;

END;

CLOSE dbCursor;

DEALLOCATE dbCursor;

http://lqqsql.blogspot.com/2009/09/sql-server-2000-maintenance-plan-non.html

Note on the start

Posted: September 15, 2009 in Uncategorized

This blog is mainly for me so I don’t loose the scripts and such that I use and carry on a usb key mainly.
If any script was written by a 3rd party I will try to reference them but some of these I have had for years and never took a note of where I got them.
Most are straight forward anyway and just evolved over time.
Some are straight forward things they I keep forgetting like how to reattach a database – I just find that when I am doing this I like to have a copy of the script in front of me.
If you find it helpful then good – if not well boo hoo:-)

I am a DBA looking after Production and Development Servers.
I was a developer years ago. Was a Developer/DBA for 2 years and then, since 2006 just a DBA.

http://lqqsql.blogspot.com/2009/09/note-on-start.html

Database Files
This script runs against the database you are connected to. This will show the total size of a database file along with the available space left in the file. This is for all the files that make up the database including the log file.

SELECT name AS ‘File Name’ , physical_name AS ‘Physical Name’, size/128 AS ‘Total Size in MB’,
size/128.0 – CAST(FILEPROPERTY(name, ‘SpaceUsed’) AS int)/128.0 AS ‘Available Space In MB’
FROM sys.database_files;

Log Files
This runs against every log file in the database giving the database name, Log Size in MB, Log Space Used (%), and Status.

DBCC SQLPERF (LOGSPACE);

DBCC SQLPERF: http://msdn.microsoft.com/en-us/library/ms189768.aspx

Free Drive Space
Shows the name of the drives and how many MB of free space it has. It does not show how large the drive is – or if there is any compression on it etc. Good to use if you a quick check to see if there is space to run a backup up.

EXEC master..xp_fixeddrives

Database Space Used
Replace tempDB with the name of the database you want to querey. Shows the database name (although you know that already), the size of the database in MB, percentage of unallocated space. It also show: total amount of space allocated by objects in the database, total amount of space used by data, total amount of space used by indexes, Total amount of space reserved for objects in the database, but not yet used.

EXEC tempDB.dbo.sp_spaceused;

You can also use this to find the size of an object:
EXEC sp_spaceused N’NameofObject’;

sp_spaceUsed: http://msdn.microsoft.com/en-us/library/ms188776.aspx

SSIS Script to show disk data on all servers populated from a table list of servers:
I have a management server with a list of servers – for this I use columns isAccessible and isSSPI. I do the isSSPI bit because this normally means I can run the WMI query due to the way the servers have been set up.

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.Collections
Imports System.Management
Imports System.IO

Public Class ScriptMain
    Dim sConn As String = “server=.;database=ServerAudit;Integrated Security=SSPI;max pool size=100;min pool size=1;”
    Dim serverNameString As String = String.Empty
    Dim serverIPString As String = String.Empty
    Public Sub Main()
        Call getServerRecordSet()
        Dim serverNameArray As String() = Split(serverNameString, “,”)
        Dim serverIPArray As String() = Split(serverIPString, “,”)
        Dim serverHDD As String = String.Empty
        Dim x As Integer = 0
        For Each Server As String In serverNameArray
            serverHDD = getDataDiskDrives(Server, serverIPArray(x))
            x += 1
            Dim aResultSet As String() = Split(serverHDD, “/*/”)
            For Each sDisk As String In aResultSet
                Try
                    If Len(sDisk) > 1 Then
                       ‘ MsgBox(“sending to db: ” & sDisk)
                        sendDatatoDB(sDisk)
                    End If
                Catch ex As Exception
                    ‘ MsgBox(“DB Failed: ” & ex.Message)
                End Try
            Next
        Next
        Dts.TaskResult = Dts.Results.Success
    End Sub

    Public Function getDataDiskDrives(ByVal server As String, ByVal ipAddress As String) As String
        Dim query1 As ManagementObjectSearcher
        Dim queryCollection1 As ManagementObjectCollection
        Dim HDDSize As String = String.Empty
        Dim thisDrive As String = String.Empty
        Try
            query1 = New ManagementObjectSearcher(“\\” + ipAddress + “\root\cimv2”, “SELECT * FROM Win32_DiskDrive”)
            queryCollection1 = query1.Get()
            For Each mo As ManagementObject In queryCollection1
                Dim HddKB As Long = 0
                Dim Hddletter As String = String.Empty
                HddKB = Convert.ToInt64(mo(“size”))
                For Each b As ManagementObject In mo.GetRelated(“Win32_DiskPartition”)
                    For Each c As ManagementBaseObject In b.GetRelated(“Win32_LogicalDisk”)
                        Hddletter = Left(Convert.ToString(c(“Name”)), 1) & “,” & (Convert.ToInt64(c(“Size”)) / 1073741824).ToString & “,” & (Convert.ToInt64(c(“FreeSpace”)) / 1073741824).ToString
                    Next
                Next
                If Len(Hddletter) > 1 Then
                    thisDrive = server & “,” & Hddletter & “/*/”
                    HDDSize += thisDrive
                End If
            Next
        Catch ex As Exception
            ‘MsgBox(ex.Message)
            HDDSize = String.Empty
        End Try
        getDataDiskDrives = HDDSize
    End Function

    Sub sendDatatoDB(ByVal dataArray As String)
        Dim SqlCommand As SqlCommand = New SqlCommand
        SqlCommand.CommandText = “[uspAddHDDItem]”
        SqlCommand.CommandType = CommandType.StoredProcedure
        SqlCommand.Connection = New SqlConnection(sConn)
        Dim DriveLetter As String
        Dim DriveSize As String
        Dim DriveSpace As String
        Dim serverName As String
        Dim resultSet As String() = Split(dataArray, “,”)
        serverName = Trim(resultSet(0))
        DriveLetter = Trim(resultSet(1))
        DriveSize = Left(Trim(resultSet(2)), 7)
        DriveSpace = Left(Trim(resultSet(3)), 7)
        If InStr(DriveSize, “.”) > 1 Then
            DriveSize = Left(DriveSize, InStr(DriveSize, “.”) + 2)
        End If
        If InStr(DriveSpace, “.”) > 1 Then
            DriveSpace = Left(DriveSpace, InStr(DriveSpace, “.”) + 2)
        End If
        Dim server As SqlParameter = New SqlParameter(“@server “, SqlDbType.VarChar, 25)
        Dim letter As SqlParameter = New SqlParameter(“@letter”, SqlDbType.Char, 1)
        Dim size As SqlParameter = New SqlParameter(“@size”, SqlDbType.VarChar, 7)
        Dim space As SqlParameter = New SqlParameter(“@free”, SqlDbType.VarChar, 7)
        server.Value = serverName
        letter.Value = DriveLetter
        size.Value = DriveSize
        space.Value = DriveSpace
        server.Direction = ParameterDirection.Input
        letter.Direction = ParameterDirection.Input
        size.Direction = ParameterDirection.Input
        space.Direction = ParameterDirection.Input
        SqlCommand.Parameters.Add(server)
        SqlCommand.Parameters.Add(letter)
        SqlCommand.Parameters.Add(size)
        SqlCommand.Parameters.Add(space)
        SqlCommand.Connection.Open()
        Try
            SqlCommand.ExecuteNonQuery()
        Catch ex As Exception
            MsgBox(“DriveSpace: ” & DriveSpace & ” DriveSize ” & DriveSize)
        End Try
        SqlCommand.Connection.Close()
        SqlCommand.Connection.Dispose()
    End Sub

    Private Sub getServerRecordSet()
        Dim sql As String = “SELECT DISTINCT ipAddress, serverName FROM servers WHERE (isAccessible = 1) AND ipAddress is not null and domainID = 1”
        Dim conn As SqlConnection = New SqlConnection(sConn)
        Dim cmd As SqlCommand = New SqlCommand(sql, conn)
        cmd.Connection.Open()
        Dim r As SqlDataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
        While r.Read()
            serverNameString += r(“serverName”).ToString() & “,”
            serverIPString += r(“ipAddress”).ToString() & “,”
        End While
        r.Close()
        conn.Close()
        conn.Dispose()
        serverNameString = Left(serverNameString, Len(serverNameString) – 1)
        serverIPString = Left(serverIPString, Len(serverIPString) – 1)
    End Sub
End Class

This is the Stored Proc for Importing the data. From this you can work out the table schema – I delete data in the table first and use server as a primary key. You could keep it and add an update data column – and change the Primary Key.
CREATE PROCEDURE [dbo].[uspAddHDDItem]
    @server as Varchar(25),
    @letter as char(1),
    @size as Varchar(7),
    @free as Varchar(7)
AS
BEGIN
SET NOCOUNT ON;
    INSERT INTO HDDAudit(diskLetter,DiskSize,FreeSpace,ServerName)
    VALUES(@letter,Cast(@size as Decimal(7,2)),Cast(@free as Decimal(7,2)),@server);
END;

Upgrading SQL Server Standard to Developer Edition on Dev and Test servers – Stand alone servers. I have also used this to upgrade back again – Developer to Standard Edition.

Make sure you have backups of your System and user databases before you begin. Take a backup of your system databases before you apply the service pack. Take another backup of them when you have finished the hot fix install.

You should probably reboot the server before you begin to make sure you have a clean slate to work with.

Create a folder called sqlAdmin on the C drive.
Create a sub folder called SQL2005
Create two sub folders called Servers and Tools.
Copy the contents of CD1 into the Servers directory and CD2 into the Tools Directory.

You will also need to copy the latest service pack and hotfixes on to the server ready for installing after the upgrade: an upgrade takes the product level down to RTM.

In the sqlAdmin directory create a text file called upgrade.cmd and add this text to it:

@echo off

Set UPGRADE = SQL_Engine, SQL_Data_Files, SQL_Tools90, SQL_Replication

Set INSTANCENAME=MSSQLSERVER

Start /wait c:\sqlAdmin\SQL2005\servers\setup.exe /qb UPGRADE=%UPGRADE% SKUUPGRADE=1 INSTANCENAME=%INSTANCENAME%
:: END OF CMD TEXT FILE

This assumes no Reporting Services or Analysis Serivces. If you have them you may need to add RS_Server, RS_Web_Interface, Analysis_Server, AnalysisDataFiles to the list in Set UPGRADE=

After the upgrade you need to install the service pack and hot fixes.
Reboot the server once you have finished.

Problems

I had a problem with the upgrade failing when Reporting Serivces was on the server “sql bpa command line has encountered a problem and needs to close”. To fix this I creted an extra dirctory under c:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\BPA\ called BPAClient and in it I copied the file BPAClient.dll from the c:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\BPA\bin\ directory.

BPA failed: http://social.msdn.microsoft.com/Forums/en-US/sqlsetupandupgrade/thread/2a5f460b-8684-4510-8fcb-1e9d9786baff
 
I have not yet managed to upgrade SSAS – SSRS was fine but SSAS maintains the same version – If I ever work this I’ll I will update this post.

This goes through all databases and finds any indexes which have fragmentation greater than 15% and reindexes them. The loop near the beginning is because sys.dm_db_index_physical_stats needs a database ID. If you do not pass a database Id and leave it as null it will go thrugh all database – which will cause a problem if it is not writeable (read_only, offline, In Recovery)

To use the null options the account running the command needs: VIEW SERVER STATE

This task will re-index and index where fragmentation is greater than 15% provided that there aare more than 50 pages used in the index. If there are less than this the chances are the index isn’t being used and Query Analyser will perform a table scan.

I took the initial idea of using this function to populate a table for reindexing from a blog but can’t remember which. I have adapted it to suit the tasks I wanted to perform.

I find STATISTICS_NORECOMPUTE = OFF a little unintuitive. It means do not not recomputed stats – or in English: recompute the stats.

On most servers this tasks takes between 5 and 20 minutes to run and I can run it every night. There are a few though where it is run instead on a weekly basis.

I also use an extra line after AND ps.index_type_desc <> ‘heap’ to exclude some databases. This is used along with AND [Name] NOT IN (‘LIST OF DBNAMES TO EXCLUDE’); The names should be like this (‘dbname’,’dbname2’). This is where the there is a database that does a lot of ETL work during the night so it is best to run a separate re-index job for that once it has finished.

If speed is a problem you can replace
FROM sys.dm_db_index_physical_stats (@dbCountID, NULL, NULL, NULL, ‘detailed’)
With
FROM sys.dm_db_index_physical_stats (@dbCountID, NULL, NULL, NULL, NULL)
This will then use the default Limited for gather statistics on the indexes – of course the faster version does not get such accurate data back.

use tempdb;

if not object_id(‘indexData’) is null
  drop table [indexData];

CREATE TABLE [dbo].[indexData](
[dbName] varchar(250) Not NULL,
[di] int Not NULL,
[oi] varchar(150) NOT NULL,
[Frag] int not null,
[pages] int not null,
[iType] varchar(250) not null,
[oName] varchar(250) NULL,
[schemaName] varchar(250) NULL);

DECLARE @dbCountID as int;
SET @dbCountID = 0;
DECLARE @topID as int;
SELECT @topID = Max(database_id) FROM master.sys.databases;
While @dbCountID < @topID
BEGIN
   SELECT TOP (1) @dbCountID = database_id FROM master.sys.databases
   WHERE database_id > @dbCountID
   AND is_Read_Only = 0 and state_desc = ‘ONLINE’
   AND [Name] NOT IN (‘LIST OF DBNAMES TO EXCLUDE’);

   INSERT INTO [indexData]([dbName],[di],[oi],[Frag],[pages],[iType])
   SELECT Distinct DB_NAME ( @dbCountID ), @dbCountID,    ps.object_id,ps.avg_fragmentation_in_percent,ps.Page_Count,ps.index_type_desc
   FROM sys.dm_db_index_physical_stats (@dbCountID, NULL, NULL, NULL, ‘detailed’) ps
   LEFT OUTER JOIN master.sys.databases AS sd
   ON ps.database_id = sd.database_id
   Where (ps.database_id > 4) AND sd.is_Read_only = 0 AND sd.state_desc = ‘online’
   AND ps.Page_Count > 50
   AND ps.avg_fragmentation_in_percent > 15
   AND ps.index_type_desc <> ‘heap’
   AND sd.database_id = @dbCountID;
END
DECLARE @dbName varchar(250);
DECLARE dbCursor CURSOR Local Fast_Forward FOR
SELECT Distinct [dbName]
FROM [indexData];

OPEN dbCursor;
FETCH NEXT FROM dbCursor INTO @dbName;
WHILE @@FETCH_STATUS = 0
BEGIN
    DECLARE @sql as Varchar(500)
    SET @sql = ‘Use [‘ + @dbName + ‘]’
    Set @sql = @sql + ‘SET ARITHABORT ON; SET QUOTED_IDENTIFIER ON;’
    Set @sql = @sql + ‘ Update tempdb..[indexData] ‘
    Set @sql = @sql + ‘ SET oName = object_Name(oi), ‘
    Set @sql = @sql + ‘ schemaName = (select schema_Name(sobj.schema_id) from sys.objects sobj where sobj.object_id = oi) ‘
   Set @sql = @sql + ‘ WHERE dbName = ”’ + @dbName + ”’;’;
   Exec (@sql);
   Use tempdb;
   FETCH NEXT FROM dbCursor INTO @dbName;
END;
CLOSE dbCursor;
DEALLOCATE dbCursor;
DECLARE @dbName2 varchar(5000);
DECLARE dbCursor2 CURSOR Local Fast_Forward FOR
SELECT Distinct ‘[‘ + dbName + ‘].[‘ + schemaName + ‘].[‘ + oName + ‘]’ as databaseObject
FROM [indexData]
WHERE oName not like ‘perfValuesForProcess%’;

OPEN dbCursor2;
FETCH NEXT FROM dbCursor2 INTO @dbName2;
WHILE @@FETCH_STATUS = 0
BEGIN
     DECLARE @sql2 as Varchar(8000);
     Set @sql2 = ‘ALTER INDEX ALL ON ‘ + @dbName2
     Set @sql2 = @sql2 + ‘ REBUILD WITH (SORT_IN_TEMPDB = ON,       STATISTICS_NORECOMPUTE = OFF);’;
     EXEC(@sql2);
     FETCH NEXT FROM dbCursor2 INTO @dbName2;
END;
CLOSE dbCursor2;
DEALLOCATE dbCursor2;
Use tempdb;
Drop Table [indexData];

Reference
MSDN sys.dm_db_index_physical_stats (http://msdn.microsoft.com/en-us/library/ms188917(SQL.90).aspx)

The set up was a SQL Server 2005 Cluster – the service was not failing over it was simply restarting. The error log would read something like – The service responded to a stop command from the cluster manager. There was no record of any problem in the SQL error log.

The first time it occurred whilst I was working here – it had happened before then – it looked like this:
At 00:29 the cluster manager issued a Stop command to the SQL Service. This caused the SQL Service to Stop. It was followed by a Start command and the service came back up again. The server was down for around 5 minutes.

There was no clue as to why it had happened except in the Windows Event Viewer which had a lot of lost communication and 19019 errors. These event errors corresponded with SQL jobs failing with server timeout errors – lost communication with the server – on tasks such as update stats in my maintenance plans (even though these are run directly on the server).

It carried on happening as I thought it was network cards and CPU affinity:
http://support.microsoft.com/kb/174812
http://www.sqlservercentral.com/Forums/Topic399630-149-1.aspx
http://support.microsoft.com/kb/892100

After a lot of tinkering and Googling it was resolved by setting Priority Boost to 0 – Microsoft recommends this for all cluster installs. After changing the setting you need to restart the SQL service. It is now servral months since this all happened and I no longer get any of these errors.
Even if you are not encountering any problems you ought to set PB to 0 (if it isn’t already) as these restarts can happen at any time.