Monthly Archives: July 2013

Rebuild SQL 2012 system databases

Posted on by

Have you ever wanted to reset a SQL server back to “factory defaults”? If you have ever accidentally trashed a SQL instance (hopefully a test/development instance) you can always reconfigure it back to a fresh install with the setup CD. This command will reset all of your users, all of your maintenance plans, everything back to the fresh install state, even if the service isn’t starting, etc.

!!Warning!! This will cause everything on the instance to be lost. Users, data, everything! Don’t perform this on a production instance!!

Insert the SQL CD and open a command prompt. Change directory into the root of the CD;

D:

Then run this command

Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=sql1 /SQLSYSADMINACCOUNTS=domain\user /SAPWD=StrongPassword

Obviously the Instance name would match your instance. If the default instance is your target, use MSSQLSERVER.

If you have changed the System database/log locations, they all will be changed back to the default locations (Data root directory).

More info is available here.
http://msdn.microsoft.com/en-us/library/dd207003.aspx

Cris.

Posted in SQL

My recipe for a basic SQL install

Posted on by

When I install SQL server on a fresh install, I chose to select different locations (drives) for the Data vs. the Log directories. Even in a virtualized environment this can be beneficial. If (for instance) you want to move the logs to a higher performance storage array, if the logs exist in a different VMDK than the data, the migration is a simple storage VMotion.

I always create 3 drives;

  • I: Drive (Install) Small
  • S: Drive (SQL Data) Large
  • L: Drive (SQL Logs) Depends on your application/db config

In the install wizard, I take the defaults for most. I only change a few options.

During the first instance install (at the feature selection screen), I select the I: drive for both the Shared Feature directories. Each additional instance these fields will be grey.

In the instance configuration screen, for the default instance I set the server name as the instance ID. I also select the I: drive (installs drive) for the Instance root directory (I:\Program Files\Microsoft SQL Server\).  The instance root directory is where SQL installer will place the binaries. Any additional instance I create I type the name of the server plus the instance name as the Instance ID. So, if you server name was SQLSERVER1, and your instance name was SQL1, the Instance ID would be SQLSERVER1SQL1.

I always select Automatic Start on the SQL Server Agent in the Server Configuration Screen.

In the Database Engine Configuration screen, I always select Domain Admins as server administrators. You can select anything you want here. These users will have SA access. I always select Mixed mode installations. I never know when I will need SQL authentication. Set the SA password to something strong!

The next tab over in the Engine Configuration screen is called Data Directories. I always select the following (don’t copy and paste here, these directories differ based upon your configuration);

  • Data root directory: S:\Program Files\Microsoft SQL Server\
  • User database directory: S:\Program Files\Microsoft SQL Server\ MSSQL11. SQLSERVER1SQL1\MSSQL\Data
  • User database log directory: L:\Program Files\Microsoft SQL Server\ MSSQL11. SQLSERVER1SQL1\MSSQL\Data
  • Temp DB directory: S:\Program Files\Microsoft SQL Server\ MSSQL11. SQLSERVER1SQL1\MSSQL\Data
  • Temp DB log directory: L:\Program Files\Microsoft SQL Server\ MSSQL11. SQLSERVER1SQL1\MSSQL\Data
  • Backup Directory: S:\Program Files\Microsoft SQL Server\ MSSQL11. SQLSERVER1SQL1\MSSQL\Backup

Again, this assumes your server name is SQLSERVER1, and your instance name is SQL1

Next, next, next…

After the installation completes you are ready to move the system databases. There is probably 1000 ways to do this, some may be faster. I do it the same way every time and it works for me. Your current layout should be as follows;

  • Master DB: S Drive
  • Master Log: S: Drive
  • Model DB: S Drive
  • Model Log: S Drive
  • MSDB DB: S Drive
  • MSDB Log: S Drive
  • TempDB DB: S Drive
  • TempDB Log: L Drive
  • Binaries: I Drive

Here is how I move the rest of the system database log files to the L drive.

**Important**
Make sure that “NT SERVICE\MSSQL$XXX” (replace XXX with the SQL instance name — use “NT SERVICE\MSSQLSERVER” for the default instance) has access to the log folder before you move the logs!!
 

Launch the SQL server Configuration manager, and then select SQL Server Services. Find the SQL server instance you just installed and double click it (It should be labeled “SQL Server(Instance Name)”, so for the sample I have been using, it would be “SQL Server (MSSQLSERVER)” for the default instance, or “SQL Server (SQL1)” for the SQL1 instance). Select the Startup Parameters tab, and locate the line that starts with;

-lS:\Program Files\Microsoft SQL Server..

Change it to read;

-lL:\Program Files\ Microsoft SQL Server...

Obviously I trancated there, but you get the point. Click “Update”. That parameter “-l” (dash L) tells SQL server where to find the Master DB log LDF file. We want to point it to the L drive.

Once you set that, right click the instance and select stop. Let it stop the agent too. Once its stopped, move the file (you might need to enable file extensions in Windows explorer);

S:\Program Files\Microsoft SQL Server\ MSSQL11. SQLSERVER1SQL1\MSSQL\DATA\ mastlog.ldf

To;

 L:\Program Files\Microsoft SQL Server\ MSSQL11. SQLSERVER1SQL1\MSSQL\Data

Start the instance back up (you can leave the agent down). Verify (through SQL Management Studio) by getting the properties on the db, and clicking the files tab and looking at the Path column.

For the Model, and MSDB, you have to run some SQL. The procedure is posted on this MSDN article.
http://msdn.microsoft.com/en-us/library/ms345408%28v=sql.90%29.aspx
They recommend to use the TSQL query;

ALTER DATABASE database_name MODIFY FILE (NAME = logical_name , FILENAME = 'new_path\os_file_name')

So, for our instance;

Use Master
ALTER DATABASE Model MODIFY FILE (NAME = modellog , FILENAME = 'L:\Program Files\Microsoft SQL Server\ MSSQL11. SQLSERVER1SQL1\MSSQL\DATA\modellog.ldf')
ALTER DATABASE msdb MODIFY FILE (NAME = MSDBLog , FILENAME = 'L:\Program Files\Microsoft SQL Server\ MSSQL11. SQLSERVER1SQL1\MSSQL\DATA\MSDBLog.ldf')

Execute that in SQL Studio, then back in configuration manager, stop the SQL instance. Move the log files (modellog.ldf, and MSDBLog.ldf) from;

S:\Program Files\Microsoft SQL Server\MSSQL11.SRVHDQDB2SDE\MSSQL\DATA

To;

L:\Program Files\Microsoft SQL Server\MSSQL11.SRVHDQDB2SDE\MSSQL\DATA

Now you can safely start SQL server and SQL agent. Again, verify your work by checking the properties on each database. While you are in there, change autogrowth file size to 10 percent on every database. Some DBA’s would explode into a ball of fire if they read that… It’s just my preference…

The lat thing I always do is change the default locations for Data, Logs and Backups. Right click on the server in SSMS (SQL Server Management Studio) and click Properties. Then click on “Database Settings”. Set the default location for Data to be;

S:\Program Files\Microsoft SQL Server\MSSQL11.SRVHDQDB2SDE\MSSQL\DATA

And set the default for the logs to be;

L:\Program Files\Microsoft SQL Server\MSSQL11.SRVHDQDB2SDE\MSSQL\Data

Congratulations, you now have a fresh installation of SQL!

Cris.

Posted in SQL

SQL Foo

Posted on by

I always need to find out whats eating disk on a MS-SQL box. Here are some queries that I use to discover what database is taking the most space;

EXEC sp_databases

This is a stored procedure that gives you DB name and size. Here is another query that you need to run on the actual DB to see the tables and their size;

SELECT 
    t.NAME AS TableName,
    p.rows AS RowCounts,
    SUM(a.total_pages) * 8 AS TotalSpaceKB, 
    SUM(a.used_pages) * 8 AS UsedSpaceKB, 
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
FROM 
    sys.tables t
INNER JOIN      
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN 
    sys.allocation_units a ON p.partition_id = a.container_id
WHERE 
    t.NAME NOT LIKE 'dt%' 
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255 
GROUP BY 
    t.Name, p.Rows
ORDER BY 
    t.Name

From: http://stackoverflow.com/questions/7892334/get-size-of-all-tables-in-database

This will give you table name, and size. Hunt down the large tables and empty some records. Don’t forget to shrink the DB after you do, so that you can reclaim that space.

Cris.