Microsoft's SQL Server is a relational database management system (RDBMS) that runs as a server providing multi-user access to a number of databases. It originated from the Sybase SQL Server codebase, which is why both products use the extension of SQL called Transact-SQL (T-SQL).
7
votes
6answers
416 views
Scheduled backup task doesn't always backup all databases despite always saying job successful
I have a job in SQL 2008 that runs a stored proc to backup all databases. This runs daily via sql server agent job.
It quits with success every day but some days it quits with success only after ...
5
votes
1answer
468 views
How to back up a small database in SQL Server 2008 R2 Express Edition
I will shortly be migrating a database from SQL Server 2000 (part of SBS 2003) to SQL Server 2008 R2 Express Edition
The database is small, and there are only a few hundred short transactions per day ...
4
votes
1answer
62 views
Are the cost percentages in this SQL Server plan over 100% for a valid reason?
I'm looking through the plan cache, looking for low-hanging optimization fruit and came across this snippet:
Why are many of the costs listed above 100% ? Shouldn't that be impossible?
4
votes
4answers
555 views
SQL Server script to delete accounts no longer in Active Directory
We have a SQL Server 2000 that will shortly be migrated to SQL Server 2005. It has years of Windows Authentication accounts created that no longer exist in Active Directory, which prevent the Copy ...
4
votes
1answer
269 views
Storage order vs Result order
This is a spin-off question from Sort order specified in primary key, yet sorting is executed on SELECT.
@Catcall says this on the subject of storage order (clustered index) and the output order
...
4
votes
1answer
221 views
Mirroring and Availability Groups in SQL2012
I've been looking at the new features of SQL 2012, and how it can help my current situation.
Currently we're on SQL 2008R2, and replicate two OLTP servers' databases to a single reporting server via ...
4
votes
1answer
468 views
Using MSDTC in SQL Server 2008
I used Hyper-v to install one windows 2008 server machine, and copy the image to create another one and change the computer name, both of them are in the same domain, and I use a domain user as ...
4
votes
4answers
5k views
SQL performance issues with remote query across linked server
This sproc
create proc dbo.Get_Accounts as
begin
declare @current_date datetime
set @current_date = dbo.fn_currdate()
select [fields]
into dbo.current_accounts
from ...
3
votes
2answers
74 views
Does SQL Server place shared locks on scanned records when using REPEATABLE READ
Assume a SQLCmd session which is using transaction isolation level REPEATABLE READ.
In this session I start a transaction and execute an UPDATE statement with a WHERE clause on a non indexed column. ...
3
votes
1answer
1k views
How to update one database from another?
I'm using SQL Server 2008. I have two databases called Adb and Bdb. There is a common table called commonTable. How can I update Adb database every 15 minutes using the data from the same table in ...
3
votes
2answers
208 views
Save history editable data RDBMS
I want to make application like testing system. Every question has one or many variant of answers (and one or many can be right). I apologize that tutors and students use my testing system. It means ...
3
votes
2answers
297 views
How can I save a severely fragmented database/table?
I've recently made a discovery that might explain my slow SQL run times.
I found out that the default in SSMS is to allow mdf growth of 1MB at a time. When the size of one table alone is roughly 23 ...
3
votes
3answers
936 views
NULLs in a composite primary key - SQL Server
I'm trying to work out the best way of creating SQL Server primary keys, foreign keys and constraints to accurately represent my data model in LINQ / Entity Data Objects. Let's assume - for the ...
3
votes
1answer
659 views
SQL Server: How to create a table that fills one 8 KB page?
Is there a way to calculate the amount of data that fills one 8 KB page in SQL Server and create a table that fills one page entirely? This kind of table is presented in in this blog post.
2
votes
4answers
69 views
What is the best permissions to set for only creating and managing own databases?
I want to create a login for a new user who could only create and manage their own databases. Other databases on the server should be read-only to that user. What would be a good set of ...
2
votes
3answers
77 views
Providing access to the SQL server database for outside developers
I am beginning a project that will involve insert and updates to a sql server database from a website. I maintain the SQL server database and am providing access to the sql server db.
My plan is to ...
2
votes
1answer
334 views
Real Time SQL Server Analysis Services Cube Processing Report
I would like to know if there is a way to generate a report to determine which SQL Server Analysis Services cubes are processing using SQL Server Reporting Services? I would like to be able to run ...
2
votes
2answers
1k views
What is the easiest way to move data from Oracle to SQL Server?
One of our products supports both Oracle and SQL Server as database backend. We have a customer who wishes to switch from an Oracle backend to Microsoft SQL Server, which isn't a typical transition ...
2
votes
2answers
164 views
Distribution database compatibility level
Using SQL Server transactional replication with a distributor on 2008R2, and publishers/subscribers all on SQL 2005. Does the compatibility level of the distribution database matter? Should it be ...
2
votes
0answers
72 views
ProcessID Owner with third party app
I am having problems trying to audit a third party app with a SQLServer backend.
The app manages users and user access via a SQL table, using a single SQL login to access the databases.
I am trying ...
1
vote
1answer
417 views
gain admin access to sql express 2008
I have inherited a Windows 7 Professional pc with an existing instance of SQL Express 2008. I have local admin rights on the machine. I do not know the SA password. I am unable to create databases or ...
1
vote
1answer
92 views
Updating SQL Server 2008 R2 using slipstreamed source
I have a requirement to create a SQL Server 2008 R2 SP1 setup media. I have done this using slipstream method as recommended by Microsoft. I can only use this to create new installation. But is there ...
1
vote
2answers
1k views
BACKUP failed to complete - with spaces, for no reason
Every day my database backup fails for some databases and does fine for others. I'm calling the backup through a Stored Procedure that runs scheduled in a job. Tonight I'm going to run the procedure ...
1
vote
1answer
362 views
SQL Server Alert Based On Severity Level Not Working
I am new to SQL Server alerts, and I am trying to setup an alert based on the severity level, that should run a job and send notifications.
In order to test if the alert works correctly I have ...
1
vote
2answers
3k views
Attach SQL Server 2008 R2 on SQL Server Express 2008
I have a database on SQL Server 2008 R2.
I want to attach it on a SQL Server Express 2008 instance running on Windows 2003.
I set the SQL server compatibility level on the database to SQL Server ...
1
vote
2answers
399 views
Should I create a maintenance plan for my database or use jobs?
I tried creating a Maintenance Plan on my SQL Server 2008 database and was impressed by some of the things that are available out of the box. However, after it was created, it seemed difficult to ...
0
votes
1answer
95 views
sql server-percentage calculation
Sample data :
LINE_NO E_FIELD F_FIELD G_FIELD HSA_STATUS FAMILY CACHE_FAMILY Count
23053B 00000 00000 00000 S SUMMIT WER 43
23053B 00000 00000 00000 T ...
0
votes
3answers
257 views
SQL services wont start due to incorrect drive letter after windows restore
SQL services wont start due to incorrect drive letter after windows restore
Had a server crash, was successfully able to reinstall windows and restore the server SQL and all the necessary services ...
0
votes
3answers
1k views
Max connections SQL Server Express could have?
I have SQL Server 2005 Express edition installed on one of my client's machine, and lots of other users are also connected to that SQL Server through the C# application that I have provided them as an ...
0
votes
3answers
2k views
sql server instance using maximum cpu usage
I have a SQL server 2005 instance installed on the server machine with two vm
Server Specification are
*Processor:* Xeon
Ram : 6GB
OS: window server on vm ware
It is running asp.net application ...
0
votes
2answers
1k views
Sql Server agent can not start
I can not start Sql Server agent to create job. Can anyone help me? .
I'm using Windows 7 Ultimate , Sql server 2008 Rc2
See this images:
0
votes
2answers
260 views
SQL Server 2012 Data Warehousing Overall Flow [closed]
I am studying Data Warehousing in SQL Server 2012
I am attempting to learn the various tools included by practicing on the different tools separately.
I have practiced with:
AdventureWorks ...
116
votes
3answers
18k views
What's the difference between a temp table and table variable in SQL Server?
This seems to be an area with quite a few myths and conflicting views.
So what is the difference between a table variable and a local temporary table in SQL Server?
26
votes
5answers
2k views
Which database could handle storage of billions/trillions of records?
We are looking at developing a tool to capture and analyze netflow data, of which we gather tremendous amounts of. Each day we capture about ~1.4 billion flow records which would look like this in ...
18
votes
2answers
3k views
What does a DBA have to know about SSAS?
I've seen a lot of material covering the business aspect of SSAS, but not really much about the important aspects of administration and management.
From the point of view of administering an instance ...
23
votes
2answers
769 views
Where can I find database tuning exercises for learning?
As a developer, often DBAs take responsibility for resolving performance issues at the database level so we don't get that much experience diagnosing, tuning, refactoring queries etc.
I'm looking ...
23
votes
6answers
4k views
What's the difference between a CTE and a Temp Table?
What is the difference between a Common Table Expression (CTE) and a temp table? And when should I use one over the other?
CTE
WITH cte (Column1, Column2, Column3)
AS
(
SELECT Column1, Column2, ...
32
votes
3answers
5k views
Guid vs INT - Which is better as a primary key?
I've being reading around reasons to use or not Guid and int.
int is smaller, faster, easy to remember, keeps a chronological sequence. And as for Guid, the only advantage I found is that it is ...
24
votes
3answers
2k views
On a dedicated database server, how much memory to reserve for the OS?
Assuming you have a dedicated server explicitly for database functions -- how much memory should you reserve for the operating system?
I realize this will probably vary somewhat depending on the ...
6
votes
1answer
9k views
How to identify which query is filling up the tempdb transaction log?
I would like to know how to identify the exact query or stored proc which is actually filling up the transactional log of TEMPDB database.
9
votes
1answer
9k views
How to find out who deleted some data SQL Server
My boss had a query from a customer yesterday asking how they could find out who deleted some data in their SQL Server database (it is the express edition if that matters).
I thought this could be ...
21
votes
7answers
1k views
What is the difference between a “record” and a “row” in SQL Server?
There was a rather innocuous question about adding dates and times in SQL Server that set off a rather fascinating taxonomic debate.
So how do we differentiate between these related terms and how we ...
17
votes
4answers
2k views
SQL Server “empty table” is slow after deleting all (12 million) records?
I have a SQL Server 2008 instance with approximately 150 columns. I have previously populated this table with approximately 12 million entries, but have since cleared the table in preparation for a ...
14
votes
7answers
2k views
Why is it a bad practice to allow everyone to use the sa login?
Even Microsoft discourages the use of SQL Server authentication mode, but our applications require it.
I've read that it's a best practice to not let users use the sa login directly, instead using ...
13
votes
3answers
2k views
Why is `SELECT @@IDENTITY` returning a decimal?
I'm using Dapper to execute the following query against a SQL Server 2008 R2 Express instance from a ASP.NET MVC 3 (.NET 4.0) application.
INSERT INTO Customers (
Type, Name, Address, ...
27
votes
3answers
8k views
Why would SET ARITHABORT ON dramatically speed up a query?
The query is a single select containing a lot of grouping levels and aggragate operations.
With SET ARITHABORT ON is takes less than a second, otherwise it takes several minutes. We have seen this ...
15
votes
4answers
818 views
Does the order of columns in a table's definition matter?
When defining a table, it's helpful to order the columns in logical groups and the groups themselves by purpose. The logical ordering of columns in a table conveys meaning to the developer and is an ...
15
votes
3answers
3k views
Which PowerShell technique should I use to talk to SQL Server?
I'd ultimately like to use PowerShell to replace the old KornShell scripts we use for SQL instance monitors. I'm having a hard time, though, getting my brain around all the different ways that ...
13
votes
3answers
1k views
Managing concurrency when using SELECT-UPDATE pattern
Let's say you have the following code (please ignore that it's awful):
BEGIN TRAN;
DECLARE @id int
SELECT @id = id + 1 FROM TableA;
UPDATE TableA SET id = @id; --TableA must have only one row, ...
12
votes
4answers
6k views
When is it OK to shrink a Database?
I know shrink is the devil: It reverses page order and is responsible for skin cancer, data fragmentation, and global warming. The list goes on... That being said, say I have a 100 GB database and I ...
