Monday, August 17, 2015

This script will load all the trace (*.TRC) files in a given folder into a single trace table. You have to specify the database to create the trace table in, the folder the trace files are in, and the filename prefix (if any). Be careful: trace files can take up a lot of space in a hurry when imported into a table.

Monday, April 20, 2015

Sometimes you run across a server that for one reason or another is full of MDF, NDF, and LDF files that look to be months or years old. Of course, you can't go by the Date Modified timestamp of these files in Windows Explorer, but still, when you see a 300 GB file called "BOBS_TEST_DB.mdf" from three years ago, doesn't your finger inch closer to the 'Delete' key? If only there was a way to tell if any databases were really using files like this...

Well now there is! This script will scan all the fixed drives, looking for MDF, NDF, and LDF files, and then verify they're not being used by any database.Once you find them, I wouldn't just delete them from the disk. The smart thing to do is move them all to another folder, wait a month for someone to complain - or some process to break - and only then delete them.

Monday, April 13, 2015

A few lines of code for those of us migrating SQL Server 2000 databases to a higher version of SQL Server and having CHECKDB fail because the counts are wrong. This simply runs DBCC UPDATEUSAGE on all the databases in the instance. I can't say it any better than BOL:

Reports and corrects pages and row count inaccuracies in the catalog views. These inaccuracies may cause incorrect space usage reports returned by the sp_spaceused system stored procedure. In SQL Server 2005, these values are always maintained correctly. Databases created on SQL Server 2005 should never experience incorrect counts, however, databases upgraded to SQL Server 2005 may contain invalid counts. We recommend running DBCC UPDATEUSAGE after upgrading to SQL Server 2005 to correct any invalid counts.

-- This can take a while....
set nocount on
declare @Sql nvarchar(4000)
set @Sql = 'RAISERROR(''?'', 10, 1) WITH NOWAIT; DBCC UPDATEUSAGE ([?])'
exec sp_MSforeachdb @Sql

Tuesday, February 17, 2015

If a database is using the FULL Recovery Model, it obviously needs to be doing log backups - otherwise the log will eventually fill the disk (your "eventually" may vary). If you don't need log backups, change the database to SIMPLE Recovery Model and sleep easier at night. But, you know how it is - one way or another, databases wind up in this mongrel state. Run this script on your oldest server - you'll probably be surprised.

This script also prints out the T-SQL command to set each database to SIMPLE Recovery Model, so you can just copy and paste.

Instant file initialization is a wonderful feature of the Windows file system - not of SQL Server, please note -- that allows the server to allocate disk pages without having to set each bit to zero. You can imagine the time savings when SQL Server is, say, adding a GB to a data file. Note that because of the way they embedded information in the first few bytes of the actual pages of the log files themselves - sigh - log files can't use Instant File Initialization. But still, it's a must-have (unless the idea of having old data laying around in your pages gives your security people the willies - best check with them). You can find lots of info about this all over the web, but here's a little script that will verify that you've jumped through all the hoops correctly.

-- Once you have added the SQL Server security account to this security policy -- you need to then execute gpupdate /force from the command prompt and restart -- the SQL Service for this change to take effect.
USE master
SET NOCOUNT ON
-- If a database named DummyTestDB_VerifyIFI already exists drop it.
IF DB_ID('DummyTestDB_VerifyIFI') IS NOT NULL
DROP DATABASE DummyTestDB_VerifyIFI
-- Temp table to hold output from sp_readerrorlog.
IF OBJECT_ID('tempdb..#SqlLogs') IS NOT NULL
DROP TABLE #SqlLogs
GO
CREATE TABLE #SqlLogs(LogDate datetime, ProcessInfo VARCHAR(20), TEXT VARCHAR(MAX))
-- Turn on trace flags 3004 and 3605.
DBCC TRACEON(3004, 3605, -1) WITH NO_INFOMSGS
-- Create a dummy database to see the output in the SQL Server Errorlog.
CREATE DATABASE DummyTestDB_VerifyIFI
GO
-- Turn off trace flags 3004 and 3605.
DBCC TRACEOFF(3004, 3605, -1) WITH NO_INFOMSGS
-- Remove the dummy database.
DROP DATABASE DummyTestDB_VerifyIFI
-- Now go check the output in the current SQL Server errorlog File. -- This can take a while if you have a large errorlog file.
INSERT INTO #SqlLogs(LogDate, ProcessInfo, TEXT)
EXEC sp_readerrorlog 0, 1, 'Zeroing'
IF EXISTS (SELECT * FROM #SqlLogs
WHERE TEXT LIKE 'Zeroing completed%'
AND TEXT LIKE '%DummyTestDB_VerifyIFI.mdf%'
AND LogDate > DATEADD(HOUR, -1, LogDate))
PRINT 'We do NOT have instant file initialization.'
ELSE
PRINT 'We have instant file initialization.'

Before making the move to SQL Server, Larry specialized in Windows programming in C/Win32, C++/MFC, and C#/.NET. His blog focuses on query tuning, database optimization, and system performance, and features handy scripts, practical tips, and occasional dispatches from the many dark corners of SQL Server.