Tag Info

Hot answers tagged

123

Take this first part with a grain of salt, because it is perhaps influenced by my having worked as a contractor for so many years. Consider looking at a contractor if your ability to pay is such that you can't attract top talent in a full-time capacity. If you're paying too little and asking for too much you're going to either get poorly skilled employees, ...


56

In SQL Server Management Studio right-click your database and select Tasks / Generate Scripts. Follow the wizard and you'll get a script that recreates the data structure in the correct order according to foreign keys. On the wizard step titled "Set Scripting Options" choose "Advanced" and modify the "Types of data to script" option to "Schema and data" ...


46

(I originally asked as a regular question but then found out the correct method - thanks BrentO) No, never. I've come across this several times now on ServerFault and want to reach a nice wide audience with some good advice. If people frown on this way of doing things, downvote and I'll remove this gladly. Auto-shrink is a very common database setting to ...


37

Though many DB vendors were very slow to do this, nearly all of them now officially support their software running in a virtualized environment. We run many Oracle 11g instances in linux on top of ESXi, and it is certainly possible to get very good performance. As with all hardware scaling, you just need to make sure that the virtualization host has plenty ...


35

Logs and data drives have different data access patterns that are in conflict with each other (at least in theory) when they share a drive. Log Writes Log access consists of a very large number of small sequential writes. Somewhat simplistically, DB logs are ring buffers containing a list of instructions to write data items out to particular locations on ...


33

Try Sql Server Database Publishing Wizard. You may need to re-order the script to make it run in one shot though because of dependencies but it will include your schema and data. If you havent got 2005 SQL XMO objects installed, you'll get an error when you run the wizard. You'll want the Microsoft SQL Server 2005 Management Objects Collection


29

Microsoft is doing all it can to make PowerShell the choice of power-users and automation writers everywhere. Gone are the days of compiling code in .NET in order to do the same thing, now you just need notepad.exe and google. We're big fans of it in the office, especially since Exchange 2007's Management Console does NOT include everything that you can do ...


28

Nope - it definitely doesn't. The only thing that allows the log to clear/truncate in the FULL or BULK_LOGGED recovery models is a log backup - no exceptions. I had this argument a while back and posted a long and detailed blog post with an explanation and a script that you can use to prove it to yourself at Misconceptions around the log and log backups: how ...


28

Obviously some machines need AV but if you need help explaining why some machines might not, here's my favorite XKCD comic...


27

Some interesting suggestions here, which all seem to show misunderstanding abot how log backups work. A log backup contains ALL transaction log generated since the previous log backup, regardless of what full or differential backups are taken in the interim. Stopping log backups or moving to daily full backups will have no effect on the log backup sizes. The ...


25

Powershell, really? what's wrong with good ol' fashioned T-SQL? sys.databases is what you want. It has human readable description columns like snapshot_isolation_state_desc SELECT snapshot_isolation_state_desc from sys.databases where name='adventureworks'


24

It was most likely caused by a query wanting to read more pages into the buffer pool, and the buffer pool grabbing more memory to accomodate that. This is how SQL Server is supposed to work. If the box experiences memory pressure, it will ask SQL Server to give up some memory, which it will do. The customer shouldn't be concerned. You can use the DMV ...


24

This can happen sometimes if you try to take a DB offline or perform certain other operations and they fail. Sometimes the lock can be cleared if you close the SSMS instance that attempted the operation, then reopen it. Close and reopen any SSMS instances attached to the server. It can also occur if you try to take the DB offline while a long query is ...


23

You'll probably want to start with the sp_spaceused command. For example: sp_spaceused Returns information about the total size of the database sp_spaceused 'MyTable' Returns information about the size of MyTable Read the docs for all the things you can get information about. You can also use the sp_msforeachtable command to run sp_spaceused against all ...


23

First, don't allow access to your SQL Server from the public Internet. The is the quickest way to get someone to break into your SQL Server. Personally if I was the sysadmin at your shop and found that you had exposed the SQL Server to the public internet while I was away so that you didn't have to bother to VPN into the network, I'd have your ass fired as ...


22

I'm sure there will be some interesting answers to this, as there is a lot of disagreement on what metrics to look at. I wrote DBCC INDEXDEFRAG, SHOWCONTIG and designed their replacements for 2005, plus wrote the Books Online content, so I'll give you my view, and explain the numbers in Books Online and the maintenance plan wizard for 2005, which I chose. ...


22

I don't think it is possible to rename without installing. There are traces left to the name in a few internal databases such as replication and you may find errors later on. If you can, unless you have more than one instance, you are best off reinstalling and then importing all your databases again.


21

Sites Most of these are multiple purpose. For instance, articles + forums + blogs. This is by no means exhaustive. It's a starting point as you'll find a lot of other blogs and resources from these sites. SQL Server Central SQL Blog SQLServerPedia SQL Skills SQL Team SQL Server Performance IT Knowledge Exchange Books This really depends on your role. ...


21

As ErikA says, this is becoming more and more common. I'm in the SQL Server camp and don't personally have any production systems running in VM's, but I would not be hesitant to (after a little more study on the topic). There are definitely some things to take into consideration before you go down that path, though (at least for SQL Server). Disk IO (as ...


20

The SQL Server Agent will do it for you. There is even a user-friendly wizard available to create the necessary backup jobs (SQL Server 2005 Books Online: How to: Create a Maintenance Plan). In terms of reliability - I would expect this to be rock-solid. It can execute multiple-step jobs at complex schedules and notify you by mail or other means or start ...


20

Ok, can you open your services console and scroll down to S for SQL Server. You should now see the services. Please ensure SQL Server (SQLEXPRESS) is running and then try .\SQLEXPRESS instead of (local). So as per your example: Server type: Database Engine Server name: .\SQLEXPRESS Authentication: Windows Authentication Hope this helps Update: These ...


20

Rebuilding a database index should not cause any data loss. It will however probably cause a substantial performance degradation as the indexes being rebuilt will normally not be available for use until the rebuild finishes. For that reason it should be done during off-hours when the affected systems are idle. Paranoia is a Good Thing in a DBA - If ...


19

I'm going to disagree with everyone here. Chuck some hardware at it. It's cheap, fast, easy, and will buy you the time needed to implement a proper CRM solution. The reason I'm advocating something that is anathema to just about everyone on not only this board, but stackoverflow as well, is that I've been a project manager/manager and have been on the ...


18

This is exactly how SQL Server is supposed to work. If you have other services on that machine and don't want SQL to consume all the available memory, you will need to set the maximum server memory. See SQL Server Memory Options on MSDN.


17

There's two parts to this question: when to add a new FILEGROUP, and when to add a new FILE in a filegroup. First let's talk theory: Mark's right about the primary reason being performance. The secondary reason is disaster recovery. With SQL Server 2005 and newer, you can do filegroup restores. When disaster strikes, you can restore just your primary ...


17

Assuming it's a user database, detach the database, move the .mdf / .ldf files to the new location and reattach the database. You might need to browse to the new location of the .ldf file when you choose your .mdf file in the reattach screen. To detach a database, right-click on it in management studio and choose Tasks | Detach. To reattach, right-click ...


17

The solution, that actually worked for me published by the author Blipsolt: shut down SQL Server from services open cmd window (as admin) and run single-user mode as local admin with this command c:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\Binn\sqlservr.exe" -m -s SQLEXPRESS open another cmd window (as admin) open sqlcmd sqlcmd -S ...


17

According to Microsoft regarding named vs default Client applications connect to an instance of Microsoft SQL Server 2005 to work with a SQL Server database. Each SQL Server instance is made up of a distinct set of services that can have unique settings. The directory structure, registry structure, and service name all reflect the specific ...


17

[wiped large answer, summarizing for clarity. See edit-history for sordid tale.] There is a single well-known SID for the local system. It is S-1-5-18, as you found from that KB article. This SID returns multiple names when asked to be dereferenced. The 'cacls' command-line command (XP) shows this as "NT Authority\SYSTEM". The 'icacls' command-line command ...


16

An SQL Server instance is a complete SQL server and you can install many instances on a machine but you can have only 1 default instance. An SQL Server instance has its own copy of the server files, databases and security credentials. This url may help you



Only top voted, non community-wiki answers of a minimum length are eligible