|
The other day, we noticed that when our CREATE DATABASE scripts were running... the databases were not created where we expected. Due to our setup, we want the databases and log files to use the SQL Server default locations. We ran the following T-SQL to verify the default locations: USE master
GO
DECLARE @SmoDefaultFile nvarchar(512)
EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultData', @SmoDefaultFile OUTPUT
DECLARE @SmoDefaultLog nvarchar(512)
EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultLog', @SmoDefaultLog OUTPUT
SELECT ISNULL(@SmoDefaultFile,N'') AS [DefaultFile], ISNULL(@SmoDefaultLog,N'') AS [DefaultLog]
Results...
| DefaultFile |
DefaultLog |
| -------------------------------- |
-------------------------------- |
| D:\SQLDbs\Data |
D:\SQLDbs\Logs |
| (1 row(s) affected) |
|
Looks correct... why is it putting the database and log files in:
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data
Just to be sure... we verified this setting on the Server Properties screen in SQL Management Studio...
Looks correct...
What we found through testing... In order for the default locations to work as expected... SQL Server must be restarted. I feel (not proven) that SQL Server loads these values from the registry at startup, and does not update when the change happens on Server Properties.
Today we noticed our transaction log backups had started failing for our user databases. From previous experience, I had a hunch that someone had created a new database that was in SIMPLE recovery model. Just an FYI... That will cause the log backup to fail. Hum... This server has hundreds of databases... Now which is the culprit. Here is a query you can use to find the recovery model of all you databases. SELECT
'DbName' = name
, 'RecoveryModel' = DATABASEPROPERTYEX(name, 'Recovery')
FROM master.dbo.sysdatabases
ORDER BY name
Now... To find which one is SIMPLE?
SELECT
'DbName' = name
, 'RecoveryModel' = DATABASEPROPERTYEX(name, 'Recovery')
FROM master.dbo.sysdatabases
WHERE DATABASEPROPERTYEX(name, 'Recovery') = 'SIMPLE'
ORDER BY name
Sure enough... There was a database in SIMPLE recovery model.
Hope that helps someone...
I have been reading this blog for some time (one of the best on SQL Server). Ya need to check out yesterday's post (a list of Microsoft Whitepapers on SQL 2008). If you want to learn more about Microsoft SQL Server and SQL coding... I HIGHLY encourage you to check out the SQL Server Code,Tips and Tricks, Performance Tuning blog. I love the quote from the site... (being a big Yoda fan & SQL Server fan & Geek) I sense many useless updates in you... Useless updates lead to fragmentation... Fragmentation leads to downtime... Downtime leads to suffering... Fragmentation is the path to the darkside... DBCC INDEXDEFRAG and DBCC DBREINDEX are the force... May the force be with you" And how their email address is protected... genius... "run the code below in Query Analyzer/Management Studio to get my email"
SELECT CONVERT(VARCHAR(31),0x73716C736572766572636F646540676D61696C2E636F6D)
Tuesday, February 12th, 2008
Bill Ryan
Topic: ADO.NET 3.5 (LINQ) and Windows Home Server! Tuesday, February 12th, 2008, is the next meeting of the Pee Dee Area .NET User Group. Bill is a typical .NET Nerd, and a Microsoft MVP. Currently, he works as a Principal Consultant and Technology Evangelist for Magenic Technologies in Atlanta, GA. He is quite active in the development community speaking frequently at users groups, code camps and web casts. In addition, Bill has authored several books and worked extensively with Microsoft and partners to develop training content for the MCTS and MCPD certification exams. In addition to spending most of his time playing with ADO.NET and the .NET Compact Framework, he helps run http://www.knowdotnet.com/ and http://www.devbuzz.com/. Part I: ADO.NET 3.5 (LINQ)
Bill Ryan will be doing an overview of ADO .NET 3.5, in particular LINQ, in preparation for an advanced ADO.NET course he'll be doing in Stockholm, Sweden. So be sure to bring your tough questions. Part II: Windows Home Server Bill Ryan will follow up with an overview on Windows Home Server cover everything from how to set it up, all the way to what all can it do. If possible, he will bring his Windows Home Server and let everyone see it for themselves. Please click the link below to register. We use this information to determine how much food to buy! 
Tentative schedule: 6:00 PM - 6:20 PM Socializing / Free Dinner
6:20 PM - 6:30 PM Introduction, Sponsor Time, and News.
6:30 PM - 7:15 PM Presentation
7:15 PM - 8:00 PM Drawing for free stuff / Wrap Up
 Last year, our local .NET User Group held a four session .NET University to cover the 3.0 Framework (WPF, WCF, WF, and CardSpace).
Now, thanks to Karl Rissland (special thanks to Doug for sharing the information), there are sessions for BizTalk. You can get all the information over at the .NET University Site (Power Points, videos, labs, etc...). If there is anyone that would like to present the material at our Pee Dee Area User Group, please let me know!!!
For those that have never heard of this product... You should check it out. Microsoft recently released a new version (FREE) downloadable here. What is it you ask... The SQL Server 2005 Best Practices Analyzer (BPA) gathers data from Microsoft Windows and SQL Server configuration settings. BPA uses a predefined list of SQL Server 2005 recommendations and best practices to determine if there are potential issues in the database environment. It provides a great deal of information about your servers. I ran it on my local SQL Server so I could show an example and it found this. Apply Cumulative Update to Improve Query Performance On versions of SQL Server before 9.0.3171.0, a known issue can degrade query performance on 32-bit and 64-bit systems with 2 gigabytes (GB) or more of memory. When you execute queries under the context of a login that is not a member of the sysadmin fixed server role, you might encounter performance degradation symptoms that arise from a large and growing Security Token cache. These issues can include performance degradation of queries, high CPU usage for the SQL Server process, and a sharp increase in worker threads and SQL user connections. Systems that have less than 2 GB of memory should not experience these issues because normal memory usage requirements keep the Security Token cache from growing too large. Best Practices Recommendations Apply the latest cumulative update package or cumulative update package #3 from the SQL Server Support page to SQL Server versions before 9.0.3171.0. Alternatively, you can apply the following hotfixes, which are all included in cumulative update package #3. - KB article 927396: Queries take a longer time to finish running when the size of the TokenAndPermUserStore cache grows in SQL Server 2005
- KB article 933564: FIX: A gradual increase in memory consumption for the USERSTORE_TOKENPERM cache store occurs in SQL Server 2005
- KB article 937745: FIX: You may receive error messages when you try to log in to an instance of SQL Server 2005 and SQL Server handles many concurrent connections
Kinda interesting stuff. Other items listed (for more examples). Keep in mind, this is my local SQL development server... don't be too critical  - Sysdtslog90 Table is found in [msdb] database on Server [slowMachine]. Do not create the Sysdtslog90 table for logging in the [master] and [msdb] databases. This is not a security related concern but could be a performance issue since SSIS can generate a lot of logging data. We recommend creating a separate database for logging.
- Login [goodUserBlah] on Server [slowMachine] has Password Policy set to [True] and Password Expiration set to [False]. We recommend that both Password Policy and Expiration must be enabled for SQL accounts.
- Service [MSSQLSERVER] on host [slowMachine] is found to be running under LocalSystem account. We recommend that SQL Server services should not run under LocalSystem account.
- Database [awesomeDatabase] on server [slowMachine] does not have PAGE_VERIFY option set to 'CHECKSUM'. We recommend setting PAGE_VERIFY option to 'CHECKSUM'.
- It's found that DBCC CheckDB for database [awesomeDatabase] on server [slowMachine] has not executed ever since the database created. We recommend you to check the database for integrity at least once in 14 days.
Anyway... you get the idea... SQL 2000 Version here. NOTE: You will need registry access to scan the SQL Server.
Coming to Florence, SC, tomorrow, December 11th, 2007!!! Pee Dee Area .NET User Groups Presents: Brian Hitney & Chad Brooks
Topic: Visual Studio 2008 Install-fest!!
Directions: http://pdanug.net Tentative schedule: 6:00 PM - 6:20 PM Socializing / Free Food / XBox 360 Play
6:20 PM - 6:30 PM Introduction, Sponsor Time, and News.
6:30 PM - 8:00 PM Presentation / VS 2008 Install-fest and Wrap Up I know I posted about this here, but I wanted to remind everyone planning on attending to please register. To do so, follow the link below: 
And if you are not currently subscribed to the Pee Dee Area .NET User Group blog, you can here  to keep informed.
Coming to Florence, SC; December 11th, 2007!!! Pee Dee Area .NET User Groups Presents: Brian Hitney
Topic: Silverlight and AJAX Development with Visual Studio 2008 + Visual Studio 2008 Install-fest!!
 We have a very special holiday event planned for December! Brian Hitney from Microsoft will be dropping in to give us an overview of Silverlight and AJAX development in Visual Studio 2008. You don’t want to miss this event because Brian will be bringing with him a FREE COPY of Microsoft Visual Studio 2008 Professional for each attendee! That’s right, a free copy to all attendees! However, there are some rules that you must follow: - You must be present to receive a copy of Visual Studio 2008 Professional.
- It is highly recommended that each attendee bring their laptop/desktop to the event, after all, this is an install-fest. For those people with desktops, we may have a few extra LCD panels at the event for general use but they will be first-come first-serve.
- Quantities of Visual Studio 2008 Professional are limited and the people who bring their computers will be first in line. In other words, if you don’t bring your computer and we run out of copies to hand out, you simply won’t receive a copy.
- These copies of Visual Studio 2008 Professional are Not for Resale. (No E-bay!!!)
Please register here so we have an idea of how many people will be attending.
Tentative schedule:
6:00 PM - 6:20 PM Socializing / Free Dinner
6:20 PM - 6:30 PM Introduction, Sponsor Time, and News.
6:30 PM - 7:15 PM Presentation
7:15 PM - 8:00 PM VS 2008 Install-fest and Wrap Up Hope to see you there!!!
 It is here... With everyone promoting the released of Visual Studio 2008...
What about SQL? Yeah I know, not yet RTM... but an update either way. Get it while it's hot! (download here) Also, Express Edition is now available with this release...
 Russ Fustino is coming to Florence, SC this Wednesday! Russ is a MSDN Developer Community Champion for Microsoft. (Register for the event here) Russ is stopping by on his way to Raleigh from Tallahassee!
FYI: A copy of Office Ultimate 2007 will be given away! Did you ever feel inundated with developer tools? Did you ever feel you are missing something that could make your life easier as a developer and as a power user? There are so many tools available to choose from and it keeps getting tougher to keep up with. Tool familiarity continues to be one of the top developers challenges. This series of seminars is all about tools for developers, by developers. Welcome to this new, fresh and original ground breaking MSDN seminar series from Russ’ Tool Shed called … It’s All About The Tools. This is the first installment of many more to come. This session is tech talk about tools for developers. Join Microsoft Florida Developer Evangelist Russ Fustino for his take on what's hot in the developer's toolbox. There's something here for everyone, from beginners to experts. Oh, by the way, you might want to put your hard hat on for this one! In this session you will see demonstrations the following tools and a few more surprises… Fiddler
Web Stress Tool
IE Developer Toolbar
Visual Studio IDE tools
code convertors
obfuscator
snippit manager (and the snippet tool in vista as a bonus)
toolbox scratch pad
Refactor
Visual Studio Express tool’s
Visual Web Developer Express:
Web Administrator
Membership and personalization
Visual Express C#
XNA Express
Live Services
Spaces
Live Writer
MSDN article on search provider (2007.08) Silverlight 1.0/1.1 XAML Designer (Expression Blend)
Community Tool Web Site Review
Top downloaded SDK’s
GotDotNet: http://www.gotdotnet.com/team/ide/
MSDN Dev Center: http://msdn.microsoft.com/vstudio/downloads/powertoys/
CodePlex home: http://www.codeplex.com/Default.aspx
Download center and more! To find out more and register for the event go here. This is going to be an awesome event! Florence, Columbia, Charleston, Conway, Myrtle Beach, Fayetteville, Wilmington, Charlotte (anyone from anyplace)... You are ALL WELCOME to attend this FREE event.
I have known about this for a while, but just recently stumbled upon it again. Here is a cool site at http://www.databaseanswers.org to assist with data model designs. They have hundreds of database designs specific for certain types of needs such as... 163+ data models... Thanks Chris Craft for first sharing the site with me!
 Windows 2008 RC0 was released yesterday. I am really looking forward to using IIS7. I am anxious to setup a few web sites with SQL 2008 and see how it works and performs.
Get it Windows 2008 RC0 here
Get SQL 2008 CTP here Cool Benefits of Windows 2008 (taken from here) - Cross-site copy allows you to easily copy Web site settings across multiple Web servers without additional configuration.
- Built-in virtualization to virtualize multiple operating systems—Windows, Linux and others—on a single server. With virtualization built into the operating system and with simpler, more flexible licensing policies, it's now easier than ever to take advantage of all the benefits and cost savings of virtualization.
- Protection of your server with security innovations that reduce attack surface area of the kernel, resulting in a more robust and secure server environment.
- Protection against malicious software with User Account Control with a new authentication architecture. (NOOOOOOOOOOO)
- Simplified management of your IT infrastructure by using new tools that provide a one-stop interface for server configuration and monitoring, as well as the ability to automate routine tasks.
- Effectively pinpointing and resolving trouble spots with powerful diagnostic tools that give you ongoing visibility into your server environment, both physical and virtual.
And the one I really want to see... - Delegated administration of applications and sites so you can give control to different parts of the Web server to those who need it.
My understanding is that this will allow web hosting companies to give more control of IIS to clients hosting their web applications on your server. Pretty cool!!! Thanks Somasegar for the announcement.
Jacob Sebastian wrote a very cool article on "power of the merge"... How many times have you needed to merge data between tables... or mobile devices to central servers... "MERGE, a new keyword which performs INSERT, UPDATE and DELETE operations at one go" His example: Old way to merge tables: 1 /*
2 Pseudo code used for saving sales order information with SQL Server 2005
3
4 -- save order header information
5 If OrderNumber found in OrderHeader
6 Update the information
7 Else
8 Insert the information
9 end
10
11 -- save order detail information
12 Delete from Order detail table all items not in the order info
13 Update Order detail for all items present in the order info
14 Insert into order details all new items in the order info
15 */ New Way using Merge 1 /*
2 Pseudo code for saving the same order with the MERGE statement of SQL Server 2008
3
4 -- save order header information
5 MERGE order info to Order Header table
6
7 -- save order detail information
8 MERGE order info to order detail table
9 */ "No, I did not miss anything. You can write the code in just 2 lines." Check out entire article here.
Tuesday, September 11, 2007, is our next meeting of Pee Dee Area .NET User's Group. Chris Craft, from ACS Technologies, will be presenting Visual Studio .NET Tips and Tricks - Part II. Material from Part I will be recovered for any of those who may have missed it, and new tips and tricks will be presented. Visual Studio .NET is filled with thousands of features and capabilities that make our lives as developers more efficient. The number of features that Visual Studio .NET contains is immense. The Visual Studio .NET Tips and Tricks presentation is a compilation of my favorite, and most popular, tips and tricks for this great IDE. Developers who are unaware of these timesaving features miss out on opportunities to increase their programming productivity and effectiveness. This Visual Studio .NET Tips and Tricks presentation is meant to explain how to use Visual Studio .NET more effectively. Here is the tentative schedule:
6:00 PM - 6:20 PM Socializing / Dinner
6:20 PM - 6:30 PM Introduction, Sponsor Time, and News.
6:30 PM - 7:45 PM Presentation
7:45 PM - 8:00 PM Drawing and Wrap Up
Please subscribe to the PDANUG Upcoming Events and News Feed here ( ).
Tuesday, August 14, 2007, is our next meeting of Pee Dee Area .NET User's Group. Chris Reeder (that's me), from ACS Technologies, will be presenting an Introduction to SQL Server Data Partitioning. Learn how to better handle billions of records in one table or how to make better use of fast and slow drive combinations. Will also explore how to speed up disaster recovery and other maintenance. We will see real world benefits and situations for using this new feature to SQL Server 2005. Here is the tentative schedule: 6:00 PM - 6:20 PM Socializing / Dinner
6:20 PM - 6:30 PM Introduction, Sponsor Time, and News
6:30 PM - 7:45 PM Presentation on SQL Data Partitioning
7:45 PM - 8:00 PM Drawing and Wrap Up Please subscribe to the PDANUG Upcoming Events and News Feed here.
sp_msforeachdb
This allows the ability to write a SQL statement for all databases.
Example:
If I want to backup all databases on my SQL Box... EXEC sp_msforeachdb 'BACKUP DATABASE ? TO DISK = ''E:\ChrisReederTemp\?.bak'' WITH FORMAT'
Notice the question mark in the SQL statement. This is the wildcard for the database name.
Jon Galloway has a pretty good post on sp_msforeachdb with a great example of backup and restores.
Do you ever need/want to monitor if your Transaction Log on SQL Server is getting full??? If so, here is a way to do it: DBCC SQLPERF(logspace)
Below is an example of the output from Books Online:
Database Name Log Size (MB) Log Space Used (%) Status
------------- ------------- ------------------ -----------
pubs 1.99219 4.26471 0
msdb 3.99219 17.0132 0
tempdb 1.99219 1.64216 0
model 1.0 12.7953 0
master 3.99219 14.3469 0
Funny site note: That status column... it will always be zero according to books online. He He...
If you have the same curiosity about the actual data file(s)...
sp_spaceused
Results from the master database:
database_name database_size unallocated space
------------- ------------- -----------------
master 16.63 1.63 MB
reserved data index_size unused
-------- ------ ----------- --------
14592 KB 10592 KB 1344 KB 2656 KB
You can find other great DBCC commands here.
 Tuesday, July 10, 2007, is our next meeting of Pee Dee Area .NET User's Group.
Chris Craft, from Taylor Data Systems, and Page Brooks, from ACS Technologies, will be presenting a follow up introduction to Silverlight, Microsoft's new platform for delivering rich internet applications (RIAs). We will further explore Silverlight and related technologies. Here is the tentative schedule: 6:00 PM - 6:20 PM Socializing / Dinner
6:20 PM - 6:30 PM Introduction, Sponsor Time, and News
6:30 PM - 7:45 PM Presentation on Silverlight
7:45 PM - 8:00 PM Drawing and Wrap Up Please subscribe to the PDANUG Upcoming Events and News Feed here.
Owner Qualification in SQL? Huh? Depending on your SQL setup and user accounts, it is possible to have the same table name in a database with different owners. I would not advise it, but SQL allows for it... And I am sure there is a reason for this... Examples: dbo.Employees
chris.Employees
sqlhero.Employees Which table does this statement get data from? SELECT *
FROM Employees Answer... It depends on your SQL username. When doing a SELECT, you could get the wrong table. (Also applies to other object types like stored procedures, views, etc) So, it is generally a good idea to qualify the owner of your SQL objects. SELECT *
FROM dbo.Employees Anyway... the primary reason for the post... There is a great article on sqlblog.com discussing an even more "real world" reason for explicitly qualifying the owner. PERFORMANCE! http://sqlblog.com/blogs/linchi_shea/archive/2007/07/05/performance-impact-of-procedure-calls-without-owner-qualification-sql-server-2000.aspx In the post, Linchi Shea does a great job of explaining the performance impact of fully qualifying your object owners. In his example, you will see in SQL 2000 it makes a BIG difference in performance. SQL 2005 is a little faster too. But, every little bit helps when you have thousands of users calling the same procedure!
| |