MS SQL Server

Databases not accessible

Just got a call that three databases on an instance where many reside were not accessible. Users were getting errors about not enough disk, memory, or the files were inaccessible. I logged into the server and found that the databases were not offline but could not be accessed. I looked into the errorlog and found the following error:

‘The transaction log for database ‘database_name’ is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases’

Well I looked around and I did not find anything wrong. Disk were logs are kept had just under 100 Gigs free and the ldf files were not in a read-only state. Based on that I offlined the databases and then attempted to bring them back online. SQL found the MDF and the LDF files, went through recovery, found no issues and brought the databases back online.

Still not sure exactly what happened. It is strange also since all the databases use the simple recovery model. Based on that I am not sure how the log was full but there you go.

MCTS Exam

So Like I said before I did take the MCTS Exam and I did pass. I can not go into any detail about the test as you have to agree to an NDA before you take it. The test was, in my opinion, not as hard as the study material. I think that MS did a great job with the study material for this test. I was not sure if I was really ready for the test but it turns out I was more than ready.
I finished the test very early. I had about 40 minutes left when I completed the question portion and still over 1 hour left when I completed the simulation portion of the Exam. To study I purchased the following kit and I am about to start on the second book to prepare for the second of the three tests required for the MCITP:DBA certification. Think I will start hitting that hard when I return from Hawaii.

 http://www.amazon.com/gp/product/0735623…

Read the book and spend time doing the practices and questions in the book and definitely take the practice tests. You will do well if you do all that.

MS Certification Test

I have scheduled my first certification test for late October. I think that by putting the date on the calendar it will push me to finish my study more quickly. I have not been as vigilent as I would have liked to be so I am lighting the fire so to speak. Also with my new position I think that I will have some big SQL Server Projects down the road pretty quickly. That is a bigger fire for me but all the extra motivation is good for me. I have learned a lot over the last 8 months or so that I have been doing DBA work with Sybase, Oracle, and SQL Server but I still feel like I have so much to learn. Now I will be focusing more on SQL Server so I am really going to start getting my hands dirty on some fun big national projects. I am very excited to be in the mix more than I have been to this point. I am sure that I will have some good stories for you very soon.
Anyway I do not have much time now to get ready for that first test. I think that I am pretty much ready but I want to study more and play in SQL Server more before taking the test. I just do better once the pressure is on. Wish me luck and I will keep you in the loop.

Powershell execute SQL Server Jobs

I have been asked to write a script that will allow users to connect to a server, get a list of only the jobs that I want them to see, and execute only those jobs. No options to do anything other than execute the job. This is what I have come up with.

[System.Reflection.Assembly]::LoadWithPartialName(’Microsoft.SqlServer.SMO’) | out-null
$s = New-Object (’Microsoft.SqlServer.Management.Smo.Server’) “Servername\instnace”
$jobserver = $s.JobServer
$jobs = $jobserver.Jobs | where {$_.Name -like “*jobname*”}
$totaljobs = $jobs.Count
$i = 1
echo “`n”
foreach ($job in $jobs)
{
echo “$i - $job.name”
$i ++
}
$exit = $i
Echo “$i - Exit”
echo “`n”
$exjob = Read-Host “What job would you like to execute`n”
if($exjob -ne $exit)
{
echo “Running Job”
$run = $exjob - 1
$jobs[$run].Invoke()
}
Echo “Exiting”

This will use integrated authentication to connect and enumerate jobs with the name criteria that you define. You will then get a numbered menu of the jobs and a prompt asking what job to run. Select the number of the job and it will execute on the server.

Please let me know if you have any questions or comments.

Moving System Databases in MSSQL2005

Recently I had to move all system databases for one server to another physical drive. This is pretty simple for all but the Master and SystemRecource databases. For all others you can just do the following and then copy the mdf and ldf files to the new location.

alter database tempdb

Modify File (Name = tempdev, Filename = ‘c:\tempdb\tempdb.mdf’);

go

alter database tempdb

Modify File (Name = templog, Filename = ‘c:\tempdb\templog.ldf’);

go

Of course with tempdb you do not need to move the mdf and ldf files and it will be recreated in the new location when the server restarts.

So for Master it is a bit different. First I would double check the current location of the master files by doing the following.

SELECT name, physical_name AS CurrentLocation, state_desc

FROM sys.master_files

WHERE database_id = DB_ID(’master’)

GO

This will give you the current location of the Master Database. Now that we know that lets move it. For master you will have to open up the SQL Server Configuration Manager utility. Highlight the SQL server 2005 Services node on the left and then find your server service on the right. Right click on it and go to Properties. Go to the Advcanced Tab and look for the Startup Options parameter. In here you will see something like the following.

-dC:\tempdb\master.mdf;-eC:\tempdb\LOG\ERRORLOG;-lC:\tempdb\mastlog.ldf

You will notice the -d -e and -l, you must leave all of those switches in the line. They are telling SQL Server the location of the Data, ErrorLog, and Log files. Then modify the path to the path that you would like it to be now. Then click apply and ok to close out of the configuration Manager. Now stop the SQL Server Service and copy the MDF, LDF files and make sure that the folder structure to the ErrorLog file is available as well as check the security and make sure that the account that runs your SQL Server has full access to this new locataion. Once that is all done pull up a command prompt so we can start the SQL Server in Single User mode.

Net Start MSSQL$Instancename /f /T3608 (for named instance)

Net Start MSSQLSERVER /f /T3608 (for default instance)

Once that starts up then do the following to move the systemresource database as well.

ALTER DATABASE mssqlsystemresource
MODIFY FILE (NAME=data, FILENAME= ‘D:\SqlData\mssqlsystemresource.mdf’);
GO
ALTER DATABASE mssqlsystemresource
MODIFY FILE (NAME=log, FILENAME= ‘ D:\SqlData\mssqlsystemresource.ldf’);

Go

now go back to the command prompt and stop the sql server by doing:

Net stop MSSQL$InstanceName (for named instance)

Net Stop MSSQLSERVER (for default instance)

Now mive the systemresource mdf and ldf files to the new location and lets start it up in single user mode again like we did above.

Now do the following:

ALTER DATABASE mssqlsystemresource SET READ_ONLY;

and now stop the service again. Now we are ready to start it up normally and if all went and goes well you should be all set. Not to big of a deal but be very careful when doing this. Make sure you have a backup of Master and make sure that you backup the original startup options just in case you need to go back. Safety first here. You do not want what should be a simple task to be a horrific event.

Thanks and let me know if you have any questions or comments.

Questions about health check script

I recieved a comment that contained a question about my previous post about writing a script to output a health check of SQL Server.

 http://itknowledgeexchange.techtarget.co…

The following is the question:

should we make anychanges for this do and what are the fields i need to provide at this columns: #Read thru the contents of the SQL_Servers.txt file
#$servers = Import-Csv “\pni-vmdbasqld$monitoringinputssqltab.txt”
$servers = Import-Csv “\ent-pocpacapcx01d$monitoringsqltab.txt”

So You should make changes to the $servers variable and point it to the input file that you are going to be using. My csv file has the following columns in it and you can adjust to yours to make it fit your needs.

Monitor,Server,Instance,TorP,ErrorLog,Ping,OS2000

Those are the things that I have found that I use in multiple scripts to deal with SQL Server. The Monitor is jsut a Y or N value to tell my monitoring scripts if I want to monitor that server at this time. Then the physical host name is the Server value, instance is NULL if default and the name of the instance if named, TorP is T if the server is Test and P if Prod, ErrorLog is the location of the errorlog on the filesystem, Ping is also a Y or N value used by my Ping Servers Script to tell it if I want it to be pinged, and OS200 tells my scripts if the server is running Server 2000 or not. That is important since OS2000 can not have Powershell installed.

Hope that helps and if you have any other questions or comments please let me know.

MR. Denny has a webcast

I just saw this and I am looking forward to attending. MR. Denny is a great SQL Resource and I am sure that I will learn new things during this webcast. Here is a link to his blog entry about the webcast.

 http://itknowledgeexchange.techtarget.co…

I suggest you check it out and learn something new from a knowledgable SQL Server DBA.

MSSQL Server Renaming Physical Host

Recently I had a server that hosts a MSSQL 2005 SQL Server named instance and the Windows group decided that the name of the server had to be changed. I thought that this may cuase an issue since the MSSQL Server name is ‘hostname\instancename’. I did a bit of research and found that a solution is available for this problem. At first I thought that I may have to reinstall a new instance of SQL Server in order to get the name correct. I did not want to go thru all of that though.

I found that MS has a couple of Stored Procs that will help with this problem.

sp_dropserver ‘hostname\instancename’ –of the old server\instancename

sp_addserver ‘hostname\instancename’, ‘local’ –of the new server\instancename

I found that when I did a select @@servername I still got the old server name returned. I also found that when I did a sp_helpserver I got the new name. I decided I would restart the server to see if that would update the @@servername variable. I right clicked on the server and said restart. After it cam back up I got a ‘NULL’ from select @@servername. I did not understand why and really still do not. I was able to resolve the issue by going to the services.msc and restarting the SQL Server Service from there. Now I see the new server name from select @@servername as well as sp_helpserver.