Cycle Those Logs!

7 08 2015

The SQL Server Error log is an extremely useful troubleshooting tool and often the first stop when trying to identify any issues. Beyond troubleshooting a DBA should be reviewing that log file on a weekly basis at a minimum, preferably daily on critical systems.

The problem is that by default the SQL Server Error log only rolls its log over when the SQL Server service restarts. Given that the goal on any production database is to keep it up and running, we are left with a log file that has months worth of data in it. So when things to go down the log file is to large to even open let alone find useful information in.

Microsoft provides a stored procedure for us to deal with this problem, sp_cycle_errorlogs. Executing this command causes the SQL Server error log to roll over and start a new log file.

Create a job that runs daily at midnight that executes the stored procedure sp_cycle_errorlogs. A log file will be created for each day, with that it is important to configure the number of logs to be retained.

Expand the Management tree in SSMS and right-click on SQL Server Logs node and select Configure.
Select the check box to limit the number of error log files and set the value to the number of days you want to keep logs local for. Typically I will configure this value to 30.





SQL Server Reporting Services – Friendly Email Name / Alias

8 04 2015

Recently when cutting over a reporting server it was asked if it were possible to assign a friendly name to the email address. There is no input text box for this in reporting services configuration and I honestly really had no idea.

Time to turn to Google.

As it turns out it is as simple as formatting the from email address in the SSRS email configuration.

Instead of just plopping in an email address and calling it a day, you can include the friendly name as well!

“Something Nice” something@abc.com

Formatting the email address in this way will show the friendly name, “Something Nice”, to recipients of the email instead of just the email address.





SQL Server – Cursor Example

25 11 2014

Cursors in SQL Server allow developers to iterate through a collection of values, normally based off a select statement. This is very useful for scripts that must apply to many items at once. More often than not that is iterating through database names to make changes to each database on an instance. Here is an example of how to iterate through all of the databases on an instance using cursors.


DECLARE @name varchar(100)
DECLARE db_cursor CURSOR FOR
SELECT name
FROM MASTER.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb')
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
print @name;
FETCH NEXT FROM db_cursor INTO @name
END
CLOSE db_cursor
DEALLOCATE db_cursor





List all Databases and Their Associated Files

30 09 2014

Sometimes we need a clear picture for a project or perhaps we have just taken over a new server to administer. We need to know certain things like what the heck is going on with all of these databases and why is the disk full?

select d.name, d.recovery_model_desc , d.is_encrypted, mf.name, mf.physical_name, ((mf.size * 8) / 1024) as 'File Size (MB)', mf.type_desc as 'type' FROM sys.databases d
Inner join sys.master_files mf on d.database_id = mf.database_id





Move Database Files Script

10 09 2014

Been a while since my last post, it has been an incredibly busy past couple of months. Getting back into the swing of things I wrote this script to make moving database files around a little easier and less time consuming. This was developed in conjunction with a project I am working on where there are hundreds of SQL Servers and each of  them has their database files scattered into the winds. It literally looks like someone took all the database files loaded them into a shotgun and blasted the server with them. Moving the databases to standardized locations is one of the many tasks I have to perform on all of these servers and moving all of those files was just to much to do by hand.


/*
Title: Move Database Files
Created: September 8th, 2014
By: Christopher Provolt

Input: Change the values for the following variables
@new_data_file_path - Path to where database data files should be moved to
@new_log_file_path - Path to where database log files should be moved to

Notes:
Ensure that the paths are set correctly prior to running scripts
Change output to text (Ctrl + T or Query > Results To > Results To Text)
Last Update
Name:
Date:

Change Log
------------------------------------------------------------------------------------------
8/9/2014 - Initial script
*/

set nocount on;

declare @new_data_file_path varchar(300)
declare @new_log_file_path varchar(300)

/* Define the new datapaths for the files to be moved to
*/
set @new_data_file_path = LOWER('<New Data File Path, Nvarchar(300),e:\mssql\data>')
set @new_log_file_path = LOWER('<New Log File Path, Nvarchar(300),f:\mssql\logs>')

/* Dynamic table to hold information on database files and their locations
we will use this data to create the required move scripts
*/

declare @info table
(
id int primary key identity,
database_id int,
dbname varchar (50),
virtual_file_name varchar(50),
type_desc varchar(10),
db_file_name varchar(50),
file_path varchar(300)
)

/* Dynamic table to hold all the scripting lines created.
This table is cleared after the data files are processed.
Steps: 1,change file location, 2, take db offline, 3, copy files, 4,database online
*/

declare @scripts table
(
step int,
scriptText varchar(300)
)

/* Dynamic table to store the changes that are to be made.
This data can be pasted into an email to inform customer of the changes
*/

declare @summary table
(
actions varchar(30),
dbname varchar(50),
file_type varchar(10),
db_file_name varchar(50),
old_path varchar(300),
new_path varchar(300)
)
/* Variable Decleration Area */
declare @id int; --id is created on temp table to use in cursor for processing data
declare @old_file_location varchar(1000) --temporarily stores the old file location for comparisons
declare @tmpString varchar(1000) -- used to temporarily hold strings
declare @temp_dbname varchar(100) -- holds temp database name
declare @temp_virtual_file_name varchar(100) -- holds temp virtual file name
declare @temp_file_type varchar(10) -- holds temp file type either ROWS or LOG
declare @temp_file_name varchar(100) -- holds temp file name
declare @temp_old_file_location varchar(1000) --holds old location of the file

/* get required data and place into @info dynamic table */
insert into @info
select mf.database_id,
db.name as 'dbname',
mf.name as 'virtual_file_name',
mf.type_desc,
REVERSE(SUBSTRING(REVERSE(mf.physical_name), 0, CHARINDEX('\', REVERSE(mf.physical_name)))) as 'db_file_name',
LOWER(SUBSTRING(mf.physical_name, 0, LEN(mf.physical_name) - CHARINDEX('\', REVERSE(mf.physical_name)) + 1)) as 'file_path'

from sys.master_files mf
INNER JOIN sys.databases db ON mf.database_id = db.database_id
where mf.database_id NOT IN
(Select database_id from sys.databases
where sys.databases.name in ('tempdb','master','msdb','model','distribution'))
/* Create the scripted commands for the data files */
PRINT('MOVE DATA FILES');

/* Create a cursor on @info table where type_desc is ROWS */
declare c cursor for select id from @info where type_desc = 'ROWS'
open c
fetch next from c into @id

while @@fetch_Status=0 Begin

/* Assign required values into variables from @info db */
select @old_file_location = i.file_path,
@temp_virtual_file_name = i.virtual_file_name,
@temp_file_name = i.db_file_name,
@temp_dbname = i.dbname,
@temp_file_type = type_desc,
@temp_old_file_location = i.file_path,
from @info i
where id = @id

/* check if we need to move the files */
if @old_file_location <> @new_data_file_path
/* if we have to move files generate the scripts */
begin
set @tmpString = 'ALTER DATABASE [' + @temp_dbname + '] MODIFY FILE (NAME=[' + @temp_virtual_file_name + '], FILENAME=''' + @new_data_file_path + '\' + @temp_file_name+ ''')';
insert into @scripts values(1, @tmpString)
set @tmpString = 'ALTER DATABASE [' + @temp_dbname +'] SET OFFLINE WITH ROLLBACK IMMEDIATE';
insert into @scripts values(2, @tmpString)
set @tmpString = 'Xcopy /O /-Y /J /Y "' + @old_file_location + '\' + @temp_file_name + '" "' + @new_data_file_path + '\' + @temp_file_name + '"'
insert into @scripts values(3, @tmpString)
set @tmpString = 'ALTER DATABASE [' + @temp_dbname +'] SET ONLINE';
insert into @scripts values(4, @tmpString)

/* Update the @summary table to reflect the change */
insert into @summary values('MOVE DATA FILE', @temp_dbname, @temp_file_type, @temp_file_name, @temp_old_file_location, @new_data_file_path)
end
else
begin
/* if there is no update neccessary update the summary table */
insert into @summary values('NO CHANGE', @temp_dbname, @temp_file_type, @temp_file_name, @temp_old_file_location, @new_data_file_path)
end
fetch next from c into @id
End

/* Close and deallocate the cursor so we can use it again for dealing with log files */
close c
deallocate c

/* Display the scripts to run */
select scriptText from @scripts order by step, scriptText

/* Erase the @scripts table prior to adding log file scripts */
delete from @scripts where scriptText <> '';
/* Create the scripted commands for the log files */

PRINT('MOVE LOG FILES')
/* Create a cursor on @info table where type_desc is LOG */
declare c cursor for select id from @info where type_desc = 'LOG'
open c
fetch next from c into @id

while @@fetch_Status=0 Begin
/* Assign required values into variables from @info db */
select @old_file_location = i.file_path, @temp_virtual_file_name = i.virtual_file_name, @temp_file_name = i.db_file_name, @temp_dbname = i.dbname, @temp_file_type = type_desc, @temp_old_file_location = i.file_path from @info i where id = @id

/* check if we need to move the files */
if @old_file_location <> @new_log_file_path
/* if we have to move files generate the scripts */
begin
set @tmpString = 'ALTER DATABASE [' + @temp_dbname + '] MODIFY FILE (NAME=[' + @temp_virtual_file_name + '], FILENAME=''' + @new_log_file_path + '\' + @temp_file_name + ''')';
insert into @scripts values(1, @tmpString)
set @tmpString = 'ALTER DATABASE [' + @temp_dbname +'] SET OFFLINE WITH ROLLBACK IMMEDIATE';
insert into @scripts values(2, @tmpString)
set @tmpString = 'Xcopy /O /-Y /J /Y "' + @old_file_location + '\' + @temp_file_name + '" "' + @new_log_file_path + '\' + @temp_file_name + '"'
insert into @scripts values(3, @tmpString)
set @tmpString = 'ALTER DATABASE [' + @temp_dbname +'] SET ONLINE';
insert into @scripts values(4, @tmpString)

/* Update the @summary table to reflect the change */
insert into @summary values('MOVE LOG FILE', @temp_dbname, @temp_file_type, @temp_file_name, @temp_old_file_location, @new_log_file_path)

end
else
begin
/* if there is no update neccessary update the summary table */
insert into @summary values('NO CHANGE', @temp_dbname, @temp_file_type, @temp_file_name, @temp_old_file_location, @new_log_file_path)
end

fetch next from c into @id
End

/* Display the scripts to run */
select scriptText from @scripts order by step, scriptText

/* Print the summary */
PRINT('SUMMARY')
select * from @summary
/* cleanup */
close c
deallocate c





Restore latest backup of database to same server as new database

30 07 2014

Some, in order to save money, do not purchase a second server for development. If development activities do not require changes to the database system then it is not a horrible idea to just use the same server. Other usage of this script is to have a copy of the production database for a sandbox, reporting, or testing. Here I wrote this script that will run in a maintenance plan to update or create a development database based off of the latest full backup of the source or production database.

/*
Name: Backup_Restore_Maintenance_Plan
Desc: Allows for a single database, the source, to be restored as a new database,
	the target, on the same server
	
Author: Christopher Provolt
Date Created: 29JULY2014

Notes:
Ensure source / target databases and the paths for the data / log files are set correctly 
prior to executing this script

*/
USE master
DECLARE @name			varchar(100)
DECLARE @location		varchar(500)
DECLARE @log_path		varchar(500)
DECLARE @data_path		varchar(500)
DECLARE @log_logical	varchar(100)
DECLARE @data_logical	varchar(100)
DECLARE @target			varchar(100)
DECLARE @source			varchar(100)
DECLARE @datapath		varchar(300)
DECLARE @logpath		varchar(300)

/* Define source and target databases */
set @source = 'AdventureWorks2012'
set @target = 'AW2'

/* define paths and file names for data and log files for target db */
set @datapath = 'E:\MSSQL12.MSSQLSERVER\MSSQL\Data\' + @target +'.mdf'
set @logpath = 'F:\MSSQL12.MSSQLSERVER\MSSQL\Logs\' + @target + '_log.ldf'

/* determine the location of the latest backup for the source DB */
SELECT  @name = x.database_name,
        @location = x.physical_device_name
        
FROM (  SELECT  bs.database_name,
                bs.backup_start_date,
                bmf.physical_device_name,
                  Ordinal = ROW_NUMBER() OVER( PARTITION BY bs.database_name ORDER BY bs.backup_start_date DESC )
          FROM  msdb.dbo.backupmediafamily bmf
                  JOIN msdb.dbo.backupmediaset bms ON bmf.media_set_id = bms.media_set_id
                  JOIN msdb.dbo.backupset bs ON bms.media_set_id = bs.media_set_id
          WHERE   bs.[type] = 'D'
                  AND bs.is_copy_only = 0 ) x
WHERE x.Ordinal = 1 AND x.database_name = @source

 /* get logical names of the source database files for recovery */
select @data_logical = name from sys.master_files where database_id = (SELECT database_id from sys.databases where name = @source) AND type_desc = 'ROWS'
select @log_logical = name from sys.master_files where database_id = (SELECT database_id from sys.databases where name = @source) AND type_desc = 'LOG'

/*Restore the database to its new home */
RESTORE DATABASE @target FROM DISK=@location
WITH REPLACE,
MOVE @data_logical TO @datapath, 
MOVE @log_logical TO @logpath




SQL Server ku5zi-bj.mdf aka The Resource Database

9 07 2014

Starting in SQL Server 2005 Microsoft introduced a hidden database called the Resource Database. The resource database contains all the system objects included with SQL Server.

The addition of the resource database was done to expedite the upgrade process, as opposed to adding, modifying, or removing system objects during the upgrade a new resource database is simply copied in and replaces the original resource database.

The objects in the resource database are made available through logical links in the sys schema of all databases.  I must point out that from experience, performing, extreme security hardening, not all of the objects are available to be seen by the mere mortal.

Want a copy of the resource database to play around with?

The easiest way to accomplish this is to grab it from the installation media, although it may be out of date if you have applied any service packs or updates.

I found the file ku5zi-bj.mdf buried deep with in the installation media folder structure. On SQL Server 2014 it is located here x86\Setup\sql_engine_core_inst_msi\PFiles\SqlServr\MSSQL.X\MSSQL\Binn.

Simply copy the database file ku5zi-bj.mdf to where ever you may be keeping your data.

Attach the database through SSMS and be sure to update the .mdf location, remove the log file, and change the name of the database to something else using the AttachAs option.