Unable to start SQL Server after relocating TempDB

After I modified the file size of tempdb database yesterday on my SQL Express instance, SQL Server was failed to start. When I checked the SQL Server error log, following entries were recorded.

2012-11-07 11:25:34.99 spid10s Error: 17053, Severity: 16, State: 1.
2012-11-07 11:25:34.99 spid10s C:\SQLServer\TempDB\TempDB_log.LDF: Operating system error 112(There is not enough space on the disk.) encountered.
2012-11-07 11:25:35.06 spid10s Error: 823, Severity: 24, State: 6.
2012-11-07 11:25:35.06 spid10s The operating system returned error 38(Reached the end of the file.) to SQL Server during a read at offset 0000000000000000 in file ‘C:\SQLServer\TempDB\TempDB_log.LDF’. Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
2012-11-07 11:25:35.11 spid10s Error: 5149, Severity: 16, State: 3.
2012-11-07 11:25:35.11 spid10s MODIFY FILE encountered operating system error 112(There is not enough space on the disk.) while attempting to expand the physical file ‘C:\SQLServer\TempDB\TempDB_log.LDF’.
2012-11-07 11:25:35.11 spid10s Could not create tempdb. You may not have enough disk space available. Free additional disk space by deleting other files on the tempdb drive and then restart SQL Server. Check for additional errors in the event log that may indicate why the tempdb files could not be initialized.

SQL Server instance was not starting because I had modified the tempDB to a bigger size then the free space available on the disk. imageTo successfully start the SQL Server instance, I need to revert back the tempdb file settings. To do that, I had started the SQL Server instance with minimal configuration, refer the following page from BOL for more details. Open the command prompt with administrative privileges and execute the following command to start the SQL Server instance:

sqlservr.exe –s SQL2008R2 /f /c

The –s switch in the above command, specifies the named instance of SQL Server. Once SQL Server instance was started with minimal configuration, the instance was also running in Single-user mode. Thus, connecting to the instance using SQLCMD, was a good choice. Executing the below t-sql commands using SQLCMD, modified the tempdb configuration. Stop the SQL server instance by pressing Ctrl + C.image

use [master];
alter database [tempdb]
	modify file (name='tempdev', filename='C:\SQLServer\TempDB\TempDB.MDF', size = 1GB);
alter database [tempdb]
	modify file(name='templog', filename='C:\SQLServer\TempDB\TempDB_log.LDF', size = 1GB);

SQL Server service was started from SQL Server Configuration Manager, issue fixed !

Hope, this may help someone. Happy Learning Smile

Posted in How To, Just Learned, SQL Server, SQL Server Configuration, SQLCMD | Tagged , , , | 7 Comments

Boot from VHD

During last user group meeting, we had a discussion on how to install Windows8RP on a VHD, without creating a separate partition. As I find that most of my friends want to try this option, so I thought of writing this post. Friends, try in on your Personal desktop/laptop; not on your corporate workstations Winking smile.

Create a VHD

Boot from VHD is feature came with Windows7. Before installing the operating system, we are going to create a VHD file. Right-click on the Computer icon, select Manage. On the Computer Management window, expand Storage and select Disk Management. Click on the Action menu, and select Create VHD as shown below: CreateVHD

On the Create and Attach  Virtual Hard Disk window, select the location, where you want to create your virtual hard disk file (in my case, its c:\temp), specify the size of the virtual hard disk file (in MB/GB/TB) and the type of virtual hard disk format (Dynamic/Fixed). For a better performance, select the VHD type as Fixed. CreateVHD_2

Once the VHD is created, take it offline and later detach it (Its my preference). OfflineVHD

In the next section, we will start the installation of of Windows8 Release Preview edition on the Virtual Hard Disk (VHD).

Installing Windows 8 RP on VHD

Now that our VHD is ready, we will start the Windows8 RP installation on this. Boot the system with Windows8 Release Preview media, press enter when you are prompted to boot from CD/DVD. Once you got the Windows Setup screen, press Shift + F10, to start the command prompt. Windows8_SetupScreen

Once the command prompt is open, start the diskpart utility. Execute the following commands:

DISKPART> sel vdisk file="D:\temp\Windows8_RP.vhd"
DISKPART> attach vdisk
DISKPART> list disk

Once you execute the above you will get the following screen: ConfigureVHD

Press Alt + Tab to get the Windows Setup screen, click on Next to continue the setup. Enter the Product-Key to continue further. EnterProductKey

Accept the Windows License terms, click Next. Select Custom: Install Windows Only (advanced) to install a new copy of Windows8 RP, click Next. In case you are not sure which disk to choose, check the total disk space from the available disks. Select the VHD from the available list and click Next to start the installation process.SelectVHD

Once the installation is completed, enter the name of your Windows8RP machine. This completes the installation of Windows8RP on a VHD. Restart your machine, and you will get the following screen: ChooseOS

Hope, it may help someone. Happy Learning Smile

Posted in Deployment, How To, UG Meet | Tagged , | 4 Comments

Hyper-V Exam: My Learning

Earlier, in June, 2012, I had an opportunity to attend a full day event on “Jump Start to Windows 2008 R2 Virtualization Exam 70-659” organized by BangaloreITPro user group & Microsoft. I received a free exam voucher for the certification exam 70-659: Windows Server 2008 R2: Server Virtualization”, as I answered one of question during the event.

How did I Prepare?

As advised during the event by most of the speakers, I started watching the following three modules on Microsoft Virtual Academy . It’s a must watch for people, who wants to learn about Hyper-V. There are 12 videos in the following three modules & if you are new to Hyper-V / Virtualization, you will find it as very helpful.

While I was looking for a preparation guide for the certification exam, I was landed on the following post by Keith Mayer, “The Study Guide” for the certification exam 70-659. I suggest, you should have a look on this guide, it has all the information you need for your exam preparation.

Security & Authorization Manager (Azman): humm.. Again a web search, and I found a series of post by John Howard, Senior Program Manager, Hyper-V. A six part series, covers in detail about Authorization Manager in Hyper-V.

Build a test lab for practice all the features. Its always better to have a hands-on experience on how to perform certain operations. Get as much hands-on as possible, there is absolutely no replacement to that. I had spend a few hours everyday on my lab environment to practice the features (although I was not able perform every task Winking smile). Talk to friends/colleagues, who are working on virtualization platform. They can provide some additional valuable input.

Watch Out

You need to look out for the following features while reading:
  • New features & functionality with Windows 2008 R2 and with Windows 2008 R2 SP1
  • Pass-through disk & disk size limitation on various types of disks
  • Hyper-V & SCVMM PowerShell Cmdlets
  • Types of Network adapters (4 legacy and 8 synthetic Network adapters are supported)
  • System Center Virtual Machine Manager 2008 R2
  • RDS Services

All The Best

I have cleared my exam on 4th September, 2012. SCVMM gave a real surprise to me. There were some scenarios, which I had never came across earlier. Incase you are using a setup with one single machine (like me), you can not perform certain actions, e.g. Live Migration. So make sure, you watch the above jumpstart series videos, specially the Management series. My friend Sahal had written a post about his learning on the JumpStart event, you can read it here.  For more information, check out the Hyper-V Survival Guide, which has a ton of resources on Hyper-V.

I wish you, all the very best for your certification exam.

Want to Join for Future Events

If you want to attend any of the future events, join BangaloreITPro User Group:

Happy Learning Smile

Posted in HyperV, UG Meet | Tagged , | Leave a comment

@PSBUG Inaugural Meet: 2 days 2 Go

I am sure, most of us are aware of this !! The Inaugural meeting of Bangalore PowerShell User Group is scheduled on 28th July, 2012 at the Dell International Services, Intermediate Ring Road, Domlur, Bangalore. The registration will be opened till 12PM today, so make sure, you have registered for the event & call your friends as well. To register for this event, click here.

The agenda for the day is as follows:

2:45 PM to 3:00 PM     –  –  –  Registrations

3:00 PM to 4: 30 PM    –  –  – Welcome Note by UG Leads

  • Introduction to PowerShell v 3.0
  • Getting Started with Windows PowerShell

4:40 PM to 4:45 PM  –  –  –  Tea / Snacks

4:45 PM to 5:30 PM –  –  – PowerShell Scripting Club

5:30 PM to 5:45 PM –  –  –  Closing Note

As some of my friends wants to receive information via social networks, here is a link to Bangalore PowerShell User Group on Facebook & twitter. The group is also listed on PowerShell group site.

Let’s learn the Power of PowerShell together !!!

Posted in PowerShell, UG Meet | Tagged , | 1 Comment

Rebuild MSDB Database

I had accidentally deleted the transaction log file of msdb database on a newly installed SQL Server 2008 R2 instance yesterday, while I was removing the old tempdb files. Although I normally copy the system database files prior removing the tempdb files from the default location, I missed it this time. When I connect to the instance, I received the following error: MSDB_FailedtoConnect

I searched in Books Online and found this article about Rebuilding System Databases, which helps in rebuild the msdb database.

Steps to Follows

  1. Stop all the SQL Server services & start the command prompt with elevated administrative privilege & execute the following command:
  2. Once you start the SQL Server with trace flag 3608, you will be able to detach the msdb database. To do that, execute the following command in SQLCMD mode:
    SQLCMD -E -S DBS03 -dmaster -Q"EXEC sp_detach_db msdb"
  3. Rename the msdb data file, and execute the instmsdb.sql file from the install folder, as shown below:
    SQLCMD -E -S DBS03 -i"E:\SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Install\instmsdb.sql" -o"E:\instmsdb.out"
  4. Review the instmsdb.out for any errors and re-apply the service packs.
  5. Stop the SQL Server.
  6. Start the SQL Server normally


Since I was able to connect to the instance without any error, I stopped the SQL Server instance and copy all the system databases files. Later I restarted the SQL Server Agent and the instance was online.

Hope, this may help someone, Happy Learning Smile

Posted in Backup - Restore, Disaster Recovery, How To, Just Learned, SQL Server, SQLCMD | Tagged , , , , , | 2 Comments

Database Files Location

“I have created a database on XXX instance, where are the physical files located?” I saw a similar post today on MSDN forum and thought of writing this short note on it. Here are the few ways to find the location of the files.

The quick way to find out the location of the data and transaction log files of a database is using the following system stored procedure. I have been using this command on a daily basis since past few weeks, while migrating databases to SQL Server 2008 R2 instances.
-- List all the available data and log file
USE [Sudeepta];
EXEC sp_helpfile;

You can also execute the following command to find the location of the data and transaction log files of a database.
-- List all the available data and log file
USE [Sudeepta];
SELECT name, type_desc, physical_name FROM sys.database_files;
We can find it by GUI as well. Connect to the instance using SSMS; expand the Databases, Right-click on the database, Select Properties. Click on the Files tab and we are presented with the location of the data and log files:
Hope, this may help someone. Happy Learning :)

Posted in How To, SQL Server, T-SQL | Tagged , , | 2 Comments

Failed Job Issue

This morning I received a mail from one of our customer stating that a SQL Server job failed many times on last Friday; however, he didn’t receive any notification about it. For a moment, I thought that I had missed to notify the customer about the failed job. I checked the job history and confirmed that the job was failed four times on last Friday and I had no clue about it. Then I started to look at all the events generated by the Operation Manager on last Friday for the same server. To my surprise, there were no alerts from that server.

Next thing, I wanted to check is the Notification tab in the Job’s Properties. FailedJob And as I thought, the checkbox next to Write to the Windows Application event log was unchecked. That’s why the failed job event was not written to the Windows event log and Operation Manager Agent was not able to display any alert on the console.

Hope this may help someone Smile Happy Learning Smile

Posted in How To, Jobs, Just Learned, SQL Server | Tagged , , , | 3 Comments