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, ...


59

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" ...


50

(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 ...


38

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


32

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 ...


30

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 ...


30

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


30

If this is something you see yourself doing often, automate it. And since you're both developers, writing some code should be in your wheelhouse. :) Seriously though... by automating it, you can do things like: Verify that you're restoring on the correct server (i.e. no dev -> prod restores) Verify that it's the right "type" of database (in your case, ...


29

On each server, you can query the sysjobs table in the msdb. For instance: SELECT job_id, [name] FROM msdb.dbo.sysjobs;


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 ...


26

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 ...


26

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 ...


26

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'


25

I disagree with the assumption in the question —this is security— but I also disagree that automation is going to save the day on its own. I'll start with the problem: You shouldn't be able to accidentally do anything to production! That includes doing automated things accidentally. You're confusing system security with concepts like "who's allowed to do ...


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 ...


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

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 ...


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

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 ...


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 ...


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 ...


19

One simple way to get Windows Server 2008 to send low disk space e-mail alerts is to use Task Scheduler and the System Log. If the free space falls below the percentage specified in HKLM\SYSTEM\CurrentControlSet\Services\LanmanServer\Parameters\DiskSpaceThreshold, an event is recorded in the System Log that can trigger a task to send an e-mail message. ...


19

SQL Server, and most other products, generate the counters all the time, no matter if there are listeners or not (ignoring the -x startup option). Counter tracing is completely transparent on the application being monitored. There is a shared memory region on which the monitored application writes and from which monitoring sessions read the raw values at the ...


18

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 ...



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