Thursday, November 6, 2008
Saturday, November 1, 2008
SqlMetal /dbml:northwind.dbml northwind.sdf
Run the Windows SDK tool <drive>:\Program Files\Microsoft Visual Studio 9.0\VC\SqlMetal.exe against your generated sdf file. Make sure you navigate to the folder containing the SDF file, typically the Visual Studio project folder. You can also generate the .vb or .cs code depending on your preference
Check out this blog post from the SQL Server Compact Team on using LINQ with SQL Server Compact
Thursday, October 30, 2008
Const ForReading = 1
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set folder = objFSO.GetFolder("d:\a")
Set outfile = objFSO.CreateTextFile("d:\testout.txt")
for each file in folder.Files
Set testfile = objFSO.OpenTextFile(file.path, ForReading)
Do While Not testfile.AtEndOfStream
'write to a single output file
Set objFSO = Nothing
Set folder = Nothing
Set outfile = Nothing
Here's how you can do it in PowerShell - with the cmdlet aliases
ls d:\a\ gc ac d:\testout.txt
This should be more than enough reason to learn PowerShell as a system administratior. More to come
Monday, October 20, 2008
That's what happened to me when I was building my virtual image of a Windows Server 2003 domain controller. I accidentally plugged in my automated installation CD. To my surprise, the installation has generated a random name for my machine. Although I could demote the domain controller, change the hostname, and promote it back, it would be very time consuming with all those reboots and manual steps. Searching thru Google gave me this site which recommends the use of the Netdom.exe tool. You do need to install the Windows Support Tools to use this tool. To change the hostname of the domain controller, open the command prompt associated with the Windows Support Tools. Then, run the following command
netdom computername oldhostname.domainname /add:newhostname.domainname
Although there are other instructions mentioned in the article, I only needed this command. After rebooting the server, I ran dcdiag.exe (Domain Controller Diagnostics Tool) and netdiag.exe(Network Connectivity Test Tool in the Windows Support Tools command prompt to validate the connectivity to the domain controllers. Knowing that you have the right tools available would make life a bit easy for any professional - whether in IT or not
Thursday, October 9, 2008
Wednesday, September 17, 2008
Monday, September 1, 2008
I've seen systems where the Everyone group is granted the same permissions on the CMD.EXE file and such is not a recommended practice due to security vulnerabilities. I'll work on a PowerShell script to check a machine for this and remove the Everyone group, if it exists.
Thursday, August 28, 2008
Next, I've expanded the appropriate files needed by the HAL and MP Kernel by running the following command in DOS
expand halmacpi.dl_ halmacpi.dll
expand ntkrnlmp.ex_ ntkrnlmp.exe
This will extract the halmacpi.dll and ntkrnlmp.exe files. Next, copy these files on your Windows directory. Type %windir%\system32 in your Run command to retrieve the Windows directory. After copying the files, modify the boot.ini using the System startup parameters option as you won't be able to do so directly using Notepad. Simply add the following items on the active boot option line. Mine looks something like this
multi(0)disk(0)rdisk(0)partition(1)\WINDOWS="Windows Server 2003, Enterprise, ACPI MPS" /fastdetect /NoExecute=OptOut /KERNEL=ntkrnlmp.exe /HAL=halmacpi.dll
I've added the ,ACPI MPS in the partition(1)\WINDOWS parameter (the comma included to separate the original Windows Server 2003, Enterprise value from what I added) and the /KERNEL=ntkrnlmp.exe /HAL=halmacpi.dll values. This will allow Windows to detect it is running on ACPI multi processor machine when it reboots and install the new MPS HAL. Try rebooting the virtual machine and once you manage to log in, it will prompt you to reboot the server after managing to install the new MPS HAL. After the second reboot, check your Task Manager and see the additional processor as your virtual Windows now recognizes the configuration you just did. I wonder if I can still do this even though I only have a single physical processor on my machine. Although that doesn't have much value, its worth trying out.
I've written an article on MSSQLTips.com to demonstrate how to use the EXCECUTE AS clause with the REVERT clause to impersonate users
Monday, August 18, 2008
After coming back to Singapore from a week of TechEd Asia 2008 mania, I've spent a lot of time thinking over what the most important lesson I've learned at the event. While a lot of attendees may talk about the great sessions they've attended or the parties they've gone to, speakers about the sessions they did (I had two breakout sessions and one instructor-led lab) and how they've given a great (or screwed up) presentation, mine was a non-technical session done by the people from Microsoft Learning entitled "If I can be a Microsoft Certified Trainer, so can you. MCT Speaks!" I am by far a part-time MCT as I no longer do full-time training. But teaching is my passion. I'd do it for free. Almost a decade ago, Ive decided to pursue the MCT certification because I know it will be my gateway to pursuing a passion in teaching (I've got other dreams about teaching but this was one of my earlier goals). One of the statements made by the moderator reminded me why I was at TechEd Asia. He said, "being an MCT gives you the fulfillment that you have made a difference in someone else's life." On the first day, after the keynote session, a guy with a camera called me while I was on my way to the presenter's room. He said he saw the video which included me promoting Microsoft certification. I was trying to recall how he knew me when he suddenly interrupted me by saying, "I still do what you have taught me about SQL Server 2000 in my work." That hit me hard. He was my trainee some five years back in a SQL Server 2000 course I did for Accenture Philippines and now he's a SQL Server consultant. He was with a few of his colleagues from Accenture and Avanade attending the week-long TechEd Asia event. I even saw one of his colleagues who happened to be one of my students way back when I was still teaching in a university. Now that's what I call fulfillment. I never imagined that they would be who they are now a few years back while they were in my classroom. But everybody has the potential to become great. I just happened to be the avenue for these people.
It's great to know that you are making a difference in someone else's life. For me, it's simply fulfilling a purpose. It just so happen that I am an MCT while fulfilling that purpose. And that is basically the reason why I volunteer to speak at these types of events, whether TechEd Asia, PASS Community Summit, MSDN TechTalks, etc. I believe that one of the people in the crowd would become somebody great someday. That in itself would make a whole lot of difference for that person. And that would be my reward
Saturday, August 9, 2008
System Center Configuration Manager
System Center Operations Manager (MVP)
Windows Server 2008
WSUS 3.0 SP1
SQL 2008 (MVP)
Word 2007 - Office Professional 2007
So, take advantage of this free offer and spread the word to those who are interested. You'll definitely enjoy the videos.
Friday, August 1, 2008
If you guys wanted to install PowerShell v2.0 CTP, the first thing you need to do is uninstall v1.0. Apparently, though, you won't find any of those usual uninstall process like using the Add/Remove Programs console or the Uninstall Windows PowerShell from your Program Files. Have no fear. You can always use your registry. Simply look for the UninstallCommand key from HKLM\Software\Microsoft\Updates\Windows PowerShell 1.0 The path would really much depend on your operating system's setup like what your service pack you are currently running. In my case, I am running Windows XP Service Pack 3 so the so the registry key for me would be HKLM\Software\Microsoft\Updates\Windows PowerShell 1.0\SP3\KB926139-v2 Run the Value Data from the UninstallCommand key to uninstall Windows PowerShell v1.0. Once you're done, you can now install v2.0. You'll be surprised that it now creates an entry in the Add/Remove Programs console. Remember, this is still in CTP so be careful not to deploy your scripts in production until its released or just bite the bullet
Thursday, July 31, 2008
So, if you haven't registered yet, it's not too late. Register now!
Tuesday, July 29, 2008
--FULL DATABASE BACKUP on Sunday
BACKUP DATABASE AdventureWorks
TO DISK = 'D:\AdventureWorks_FULL.BAK'
WITH INIT, FORMAT, STATS=10
--FULL DATABASE BACKUP ON Wednesday CREATED BY YOUR DEVELOPERS
BACKUP DATABASE AdventureWorks
TO DISK = 'D:\AdventureWorksD.BAK'
WITH INIT, FORMAT, STATS=10
--DIFFERENTIAL DATABASE BACKUP AFTER THE LAST FULL BACKUP
BACKUP DATABASE AdventureWorks
TO DISK = 'D:\AdventureWorks_DIFF.BAK'
WITH DIFFERENTIAL, INIT, FORMAT, STATS=10
If you're the DBA, you might be thinking that you still have your backup sequence intact and this is what you might have done should you need to restore
--RESTORE SEQUENCE STARTS
RESTORE DATABASE AdventureWorks
FROM DISK = 'D:\AdventureWorks_FULL.BAK'
WITH REPLACE, NORECOVERY, STATS=10
--FAIL DUE TO INCONSISTENT RESTORE SEQUENCE
RESTORE DATABASE AdventureWorks
FROM DISK = 'D:\AdventureWorks_DIFF.BAK'
WITH REPLACE, NORECOVERY, STATS=10
Now, there are a couple of ways to solve this problem. One is to create a trigger on your MSDB database to trap events written on your backupset system table and check for the type column for D values. At least you get to be notified for any FULL database backups outside of your normal backup process. In SQL Server 2005, it's as easy as using the COPY_ONLY option in the BACKUP command. This specifies that the backup is a copy-only backup, which does not affect the normal sequence of backups. Make sure that your operational DBAs and developers who work on SQL Server 2005 know how to use this option whenever thay run ad-hoc backups. Here's how to use the COPY_ONLY clause on your BACKUP commands (with emphasis on COPY_ONLY)
--FULL DATABASE BACKUP on Wednesday
BACKUP DATABASE AdventureWorks
TO DISK = 'D:\AdventureWorksD.BAK'
WITH INIT, FORMAT, STATS=10, COPY_ONLY
Monday, July 28, 2008
Friday, July 25, 2008
Msg 824, Level 24, State 2, Line 1
SQL Server detected a logical consistency-based I/O error:
incorrect checksum (expected: 0xf93a020c; actual: 0xf93a820c).
It occurred during a read of page (1:69) in database ID 10 at offset 0x0000000013c000 in file
'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\SalesDB.mdf'.
Additional messages in the SQL Server error log or system event log may provide more detail.
This is a severe error condition that threatens database integrity and must be corrected immediately.
The first thing you need to do after a database restore is to check the MSDB.dbo.suspect_pages to see if there are suspect pages that need to be repaired. Books Online describes the structure of this system table and gives you an idea on what to restore should there be suspect pages. Bear in mind though that this system table can only hold up to a maximum of 1000 records and everything get cycled out so you need to do your own housekeeping.
The Enterprise Edition gives you an option to do a page-level restore should you have corrupt databases. A more low-level discussion on fixing damaged pages can be found on the SQL Server Storage Engine blog
Tuesday, July 22, 2008
To use the MIRROR TO clause, see the sample script below
BACKUP DATABASE AdventureWorks
TO DISK = 'D:\AdventureWorksDB2.BAK'
MIRROR to DISK = 'F:\AdventureWorksDB.BAK'
WITH INIT, FORMAT, STATS=10
This creates a mirrored copy of the database backup of the AdventureWorks database to the F:\ drive. Although this creates a mirrored copy of the backup, it will definitely take quite some time to complete as it is writing on all the mirrored media set, thereby, increasing database your backup window. I did a test on this by using the MIRROR TO DISK clause with the AdventureWorks database and it takes like 10 times longer to do a backup with this option - the speed, of course, would be dependent on where you mirror your backups and its IO performance
I'd probably still stick to using the copy command in my backups as long as I get the same result. For more information on using mirrored backup media sets, check out this MSDN article
Wednesday, July 16, 2008
MSDN has a documentation on what this is all about but let me tell you one thing - you MUSt include this in your backup procedures. If you want to have an idea of how critical these files are, stop your SQL Server 2005 service and rename the mssqlsystemresource.mdf file. You won't be able to restart your SQL Server service afterwards
Tuesday, July 15, 2008
Now, this doesn't mean only web applications are prone to SQL injection attacks. Even Windows-based applications are so make sure you check them as well.
Wednesday, July 9, 2008
Tuesday, July 8, 2008
$strFilter = "(&(objectCategory=User))"
$Dom = 'LDAP://DC=yourDomain;DC=LOCAL'
$objDomain = New-Object System.DirectoryServices.DirectoryEntry $Dom
$objSearcher = New-Object System.DirectoryServices.DirectorySearcher
$objSearcher.SearchRoot = $objDomain
$objSearcher.PageSize = 1000
$objSearcher.Filter = $strFilter
$objSearcher.SearchScope = "Subtree"
$colProplist = "name", "pwdlastset"
foreach ($i in $colPropList)
$colResults = $objSearcher.FindAll()
foreach ($objResult in $colResults)
$objItem = $objResult.Properties
Function Integer8Date(ByVal objDate, ByVal lngBias)
' Function to convert Integer8 (64-bit) value to a date, adjusted for
' local time zone bias.
Dim lngAdjust, lngDate, lngHigh, lngLow
lngAdjust = lngBias
lngHigh = objDate.HighPart
lngLow = objdate.LowPart
' Account for bug in IADslargeInteger property methods.
If (lngLow < 0) Then
lngHigh = lngHigh + 1
If (lngHigh = 0) And (lngLow = 0) Then
lngAdjust = 0
lngDate = #1/1/1601# + (((lngHigh * (2 ^ 32)) _
+ lngLow) / 600000000 - lngAdjust) / 1440
Integer8Date = CDate(lngDate)
I pass the value from pwdLastSet property to this function to get the date/time value. After a couple of Google searches, I chanced upon a forum post by Brandon Shell - Microsoft PowerShell MVP. The entire function can be summed up in just a line
This is if you are using the System.DirectoryServices.DirectorySearcher class and not the
System.DirectoryServices.DirectoryEntry in the .NET Framework which is available in PowerShell. The DateTime.FromFileTimeUtc Method converts the specified Windows file time to an equivalent UTC time. So much for using conversion functions in VBScript to do this.
Thursday, July 3, 2008
strComputer=Wscript.Arguments.Item(0) 'parameter passed = hostname
wmiQuery = "Select * From Win32_PingStatus Where Address = '" & strComputer & "'"
Set objWMIService = GetObject("winmgmts:\\.\root\cimv2")
Set objPing = objWMIService.ExecQuery(wmiQuery)
For Each objStatus In objPing
If IsNull(objStatus.StatusCode) Or objStatus.Statuscode<>0 Then
Reachable = False 'if computer is unreacable, return false
Reachable = True 'if computer is reachable, return true
Here's the equivalent script using PowerShell
$ping = new-object System.Net.NetworkInformation.Ping
$Reply = $ping.Send($hostname)
You can see how much easier it is to accomplish a simple task in PowerShell. It makes the life of an administrator much better, especially if you have to write scripts that automate repetitive tasks. I'll post more samples of the VBScript codes you've probably seen in this blog converted to PowerShell
Tuesday, July 1, 2008
You can find out more about execution policy in Windows PowerShell by typing Get-Help Set-ExecutionPolicy on your PowerShell command window. You should also make sure that you have permissions to run scripts on your machine. Check either your local or your domain security policy to be sure. Plus, unlike VBScript where you can just double-click and run, PowerShell scripts don't behave that way. You need to call PowerShell before running the script. This is to make sure you don't accidentally run the script by just double-clicking. It does make sense especially if your machine get's exploited by a virus or a worm.
Thursday, June 26, 2008
This will allow you to step over your code - whether it is calling a stored procedure or a TSQL script - and step thru it a line at a time or at one shot. You can even step thru nested calls, like calling a stored procedure that calls another stored procedure. It will open up a new tab displaying the codes for the procedure or function that you are calling from your TSQL code. I'm just glad it's already in RC0. Check out my video from BlogCastRepository.com on how this works
Monday, June 23, 2008
Wednesday, June 18, 2008
Check out the BlogCastRepository website for my SQL Server 2008 videos. I'll come up with SQL Server 2005 videos as well in the future
Saturday, June 7, 2008
Monday, June 2, 2008
I have been trying to convince developers how a functional application can be vulnerable if they do not implement security best practices in writing codes. I come from a developer background as well and with tight and unreasonable deadlines, developers are only concerned with functionality and nothing more. But with the increasing incidents of websites being defaced because of SQL injection attacks, there's no doubt that as long as applications are running on servers, there will always be security vulnerabilities. SQL injections attacks are no respecter of platforms nor database engines. So better have a look at your application codes.
A Microsoft blog post on SQL injection attack is available here.
Friday, May 30, 2008
Monday, May 26, 2008
Step 1: Stop any service that is accessing the database
Since this is a WSUS database, we need to stop several services like the Update Service and the WWW service. You can do so using the Services applet or by using the NET STOP command
Since we do not have Enterprise Manager by default in using MSDE, we will stick to our command-line tool, osql. The sp_detach_db command detaches the specified database from SQL Server/MSDE. If you didn't stop any service or application accessing this database, this command will fail. Below is the syntax for the sp_detach_db command(see MSDN as well)sp_detach_db [ @dbname= ] 'database_name'
[ , [ @skipchecks= ] 'skipchecks' ]
To use the sp_detach_db in osql for the SUSDB database, execute this in the command line
osql -E -S %computername%\wsus -Q "exec sp_detach_db 'SUSDB'"
I am more comfortable with executing TSQL scripts while logged in that's why I make it a point to secure a logged in connection first before I execute them.
Step 3: Move the database files to a new location
Now that you have disconnected the database from the server, you can now treat it as any other file in your file system. Move the SUSDB.mdf and SUSDB_Log.LDF files (for any database, be sure you know which files correspond to which database by executing the sp_helpdb command)
Step 4: Re-attach the database using the sp_attach_db command
Execute the sp_attach_db in osql to re-attach the database files you have moved to a new location.
osql -E -S %computername%\wsus -Q "exec sp_attach_db @dbname=N'SUSDB', @filename1=N'E:\WSUS\MSSQL$SUS\Data\SUSDB.mdf', @filename2=N'E:\WSUS\MSSQL$SUS\Data\SUSDB_log.ldf'"
Step 5: Start the services which you stopped in Step 1
Resume the services or applications you stopped. Check whether the application still works fine and that it can still access the database.
This is a generic approach which can be used for any database running on MSDE or SQL Server, whether it's WSUS or not. Note that if you are dealing with WSUS 3.0 which, by default, uses the Windows Internal Database (SQL Server 2005 Embedded Edition), check out this blog post to connect to this instance.
Thursday, May 15, 2008
This post was originally posted ni this blog entry
It's quite shocking to hear the word "undocumented" in SQL Server. This simply means that you can break your neck trying to find these topics in both the Books Online or Microsoft.com and you won't find anything. But there's always Google to take your chances. As I was trying to search for a VBScript to check for an existence of a file in a file system (it would be a lot easier in .NET but I'm limited to what I just have) when I chanced upon this extended stored procedure - xp_fileexist. DBAs do file system checks whenever they do data transformation or data loading from a delimited text file, an Excel spreadsheet or anything similar. If this was done by a batch job you need to check first whether a file exists based on a specific filename format (such as Test_yyyymmdd.xls). You can create a simple stored procedure to check for the formatting of the filename but to check whether the file exists before you to do your task is not something that you can do with SQL Server. Good thing there is such a stored procedure - xp_fileexist. This verifies whether a specified file exist. This stored procedure is located in the master database where you can execute it in the following syntax:
DECLARE @result INT
This will return three columns indicating if the file exists, if the file is a directory and if the file's parent directory exists as a result set. You may want to include this in a script to do functional checks such as the example below
EXEC xp_fileexist 'c:\autoexec.bat', @result OUTPUT
IF (@result = 1) --@result will return 1 if the specified file exists, if it doesn't, it will return 0
PRINT 'file exists'
PRINT 'file does NOT exist'
This is also helpful when you want to check for the existence of a database backup file as it has been recorded in the msdb database. One of the challenges I have is checking if a database transaction log backup still exists if a log shipping job starts failing.
Just remember that when using this stored procedure is a bit risky as Microsoft reserves the right to change or even remove this in future versions or even service packs without having any documentation (well, it really doesn't have any after all) so make sure to test your scripts after applying service packs or upgrading.
Thursday, May 8, 2008
I was deploying an image to a server and wanted to try out VMWare Player instead. I am using version 2.0.3. Since the server has 4 processors, I was thinking of configuring the image to use as much processors as are available. Now there are two ways to do this. First, you can use VMWare Workstation or Server to change the configuration of the VMX file. You can use the graphical user interface for this. In my case, since my laptop does not have multi-processors, I did encounter an error stating that the image will not start if I use more than one CPU. But it doesn't matter since I am not going to start the image on my laptop anyway. I just need to change the settings so I can use those settings on a different machine.
Second, similar to how you do it in Microsoft Virtual PC where you change the VMC file, you can change the VMX file by searching for the numvcpus field and changing the value of the number, let's say from 1 to 2.
Oh, and there's one more thing: VMWare Player does not support more than 2 processors. And so I found out
Tuesday, April 29, 2008
Monday, April 21, 2008
Multiple connections to a server or shared resource by the same user, using more than one user name, are not allowed
Wednesday, April 16, 2008
Tuesday, April 8, 2008
The name or security ID (SID) of the domain specified is inconsistent with the trust information for that domain
Fortunately, SysInternals have a tool called NewSID v4.10. This tool is a program that changes a computer's SID. It is free and is a Win32 program, meaning that it can easily be run on systems that have been previously cloned. NewSID works on Windows NT 4, Windows 2000, Windows XP and Windows Server 2003. You can download the tool from Microsoft and run it on the cloned machine to change its SID. It is recommended though that you run this tool on a machine that is not a member of a domain. You can choose to randomly generate a SID, copy an SID from another computer or simply specify one (which is not really recommended). You can also choose to rename the machine to a different one (in my case, I still needed to run this tool eventhough I've already renamed the cloned image since they are both going to be a part of the same domain). A rebot would be required after a SID change has been applied. Download the tool and try it out for yourself
Monday, March 31, 2008
sqlmetal /server:localhost /database:Northwind /language:cs /code:Northwind.cs
The command generates a .cs file from the Northwind database running on my SQL Server 2000 instance. When you open the code in Visual Studio 2008, you will see the codes which may seem familiar if you have used the designer to create LINQ objects. The code includes all the database entities and their corresponding attributes, meaning the tables and their fileds with constraints and data types. If you need to include stored procedures and functions, you can simply add the /sprocs and /functions switches. If you're thinking of simply ignoring this tool since the designer is already available for you, think again. It only took me like 10 seconds to type the syntax and it generated the code for me. Compare that to using the designer and dragging and dropping the database objects which would take me at least a minute or two. This would be a great time saver if you need to generate codes for about 50 databases scattered in different SQL Server instances in your environment. Cool tool for developers
Thursday, March 27, 2008
For more information on SQL Server 2008 Policy Based Management, you can check out a virtual hands-on lab at the TechNet Virtual Lab site so you can play around with how it works. The SQL Server Manageability Team also came up with a Level 300 webcast sometime February 2008 which digs deeper on this new feature in SQL Server 2008. Plus, I've written an article for MSSQLTips.com and created a video at BlogCastRepository.com on this as well - more on evaluating policies on multiple instances at the same time.
Sunday, March 23, 2008
Friday, March 14, 2008
Having to work in an organization that provides enterprise hosting services, I regularly deal with customers who blame our infrastructure whenever their application runs slow. Having just upgraded some servers on a new hardware and on a new platform - from Windows 2000/SQL Server 2000 to Windows Server 2003/SQL Server 2005 - I have to prepare a few bullets to shoot to our customers if they start complaining about performance issues. My take on this is that no amount of hardware upgrade is more than enough to compensate for poorly written application (scripts included). Let me start by introducing SQL Server 2005's dynamic management views (DMV) and functions (DMF). DMVs (not the Department of Motor vehicles) return server state information that can be used to monitor the health of a server instance, diagnose problems, and tune performance. In SQL Server 2000, you will have to use a ton of tools just to get these information. Not so with SQL Server 2005. Let me start by looking at the sys.dm_os_schedulers view. Note that DMVs belong to the sys schema and have dm_ as their prefix. This view returns one row per scheduler in SQL Server. Each scheduler is mapped to an individual processor in SQL Server (in multi-core, hyper-threaded processors, this count is on a per-core/thread basis). You can use this view to monitor the condition of a scheduler or to identify runaway tasks. When do you use this view? You use it when you feel that the CPU is where your bottleneck happens. To illustrate, let's take a look at a sample script below
SELECT scheduler_id, current_tasks_count, runnable_tasks_count, active_workers_count, work_queue_count, current_workers_count, is_idle, pending_disk_io_count
WHERE scheduler_id < 255
Some pointers about the script - since the scheduler is represented by the number of processors in your server, you should technically have the same number for the same number of processors. On an 8-processor, dual-core server, you will have 16 schedulers. The reason we limit our query to those with scheduler_id value less than 255 is that anything IDs greater than or equal to 255 are used internally by SQL Server, such as the dedicated administrator connection scheduler. How can we use the information returned by this query? The number of runnable tasks is generally a non-zero value; a nonzero value indicates that tasks have to wait for their time slice to run. Imagine having to wait for your turn just to get some ice cream. Performance wise, the ice cream vendor (your scheduler, in this case) is the bottleneck. With runnable_tasks_count returning 0, there are no actively running tasks. However, active sessions may exist like probably a dedicated administrator connection. High values for this counter are a symptom of a CPU bottleneck. The "high value" term here is probably relative as some say "high value" means greater than 2 or greater than 10. Whatever the values may be, a recommendation would be to come up with a baseline as there are different baseline values for different systems. There are other information you can get out of this DMV. For example, you can look at the pending_disk_io_count column to determine if you have a disk IO problem. Similar to the runnable_tasks_count column, the higher the value for this, the more likely that your IO is your bottleneck. A whitepaper to troubleshoot performance issues in SQL Server 2005 is available from TechNet and provides a better insight. We'll look at the other DMVs and DMFs* in future posts
*Note: DMFs are also the former acronym for SQL Server 2008's Declarative Management Framework. To minimize confusion, they now call DMFs as Policy Based Management in SQL Server 2008
Wednesday, March 12, 2008
To know more about this tool, you can download a Microsoft webcast that focuses on how to use this tool
Saturday, March 8, 2008
TITLE: Microsoft SQL Server Management Studio
Exception has been thrown by the target of an invocation. (mscorlib)
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.(provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 53)
I checked the maintenance plan and verified the connections and saw that it was the instance name of my test server. I checked the SQL Server logs on my test server and noticed that my production server's SQL Server service account was trying to connect. Good thing I applied best practice here or I would end up deleting the database maintenance plan on the test server from the production server. So, how do I do it? The workaround is to check the dbo.sysmaintplan_plans table of the MSDB database. The ID column can be used as a value for us to delete records in the dbo.sysmaintplan_log, dbo.sysmaintplan_subplans and dbo.sysmaintplan_plans tables. Once those records have been deleted, you can refresh your Maintenance Plans folder in SQL Server Management Studio and notice the maintenance plans disappear. This does not delete the jobs created by these maintenance plans. You still have to manually delete those jobs.
Sunday, February 24, 2008
NOTE: If the value of Listen All on the Protocol tab is yes, the TCP/IP port number for this instance of SQL Server 2005 is the value of the TCP Dynamic Ports item under IPAll. If the value of Listen All is no, the TCP/IP port number for this instance of SQL Server 2005 is the value of the TCP Dynamic Ports item for a specific IP address.