This tag is specifially for the R2 version of Microsoft's SQL Server 2008.
3
votes
5answers
2k views
Convert varchar column to datetime
In SQL Server, I have a varchar column with value such as:
07/May/2012:00:06:22
07/May/2012:00:06:22
07/May/2012:00:06:22
07/May/2012:00:06:22
07/May/2012:00:06:22
QUESTIONS
How do I convert ...
26
votes
4answers
8k views
What are Objective Business Reasons to Prefer SQL Server 2012 over 2008 R2?
My company is facing the decision whether to purchase SQL Server 2012 Denali or SQL Server 2008 R2 for a new database server. I am looking for objective reasons to choose one over the other.
Our ...
16
votes
2answers
4k views
I/O requests taking longer than 15 seconds
Usually our weekly full backups finish in about 35 minutes, with daily diff backups finishing in ~5 minutes. Since tuesday the dailies have taken almost 4 hours to complete, way more than should be ...
6
votes
2answers
1k views
How to create an Event Notification that runs a Job/procedure when mirroring state changes
I'm asking this question in the sequence of this one Can I send a string over TCP using T-SQL?
Remus Rusanu exposes what it seems to be an optimal solution for my problem, but... I'm too immature to ...
5
votes
1answer
730 views
Trouble With Database Permissions for sp_send_mail
I am trying to send database mail but I am getting EXECUTE permission denied on the object 'sp_send_dbmail' database 'msdb', schema 'dbo'.. I code I am running is as follows:
SELECT SUSER_NAME(), ...
6
votes
3answers
228 views
Would it be considered as a bad practice to have multiple nullable FKs on a table in SQL Server
On my database structure in SQL Server, I have 3 types of products which requires different information about the order. So, I created one Customers table and three different orders tables: ...
2
votes
2answers
4k views
SQL Server Transaction Timeout
Is there a way in SQL Server 2008 R2 to cause a timeout for a database modification involving a transaction? We have a scenario where our application code hangs or throws an exception and fails to ...
-3
votes
1answer
209 views
Why does a VIEW with a CLUSTERED index not use the index?
I have a view that has a clustered index along with other non-clustered indexes.
A simple SELECT query does not use any of the indexes even when I select an indexed column.
What could be the ...
8
votes
1answer
294 views
Behavior of data in indexes based on fill factor
Let's say you have a database where the default fill factor is 20. Whenever data is inserted, does it only create pages filled up to 20%?
From my understanding, when the data is inserted there ...
7
votes
2answers
2k views
SQL Server 2008 - Partitioning and Clustered Indexes
So let me preface by saying I do not have total control over my db design, so a lot of the aspects of the current system cannot be changed for the purposes of this scenario.
Comments about how we ...
6
votes
2answers
183 views
Can I use transaction logs to rebuild a database?
We've had some data loss and don't have a reliable backup it seems. We do have HUGE transaction logs and there was some thought that we might be able to use these to get back to a certain point.
Is ...
5
votes
2answers
561 views
SQL Server gets slow over time until we have to restart it
We have a database with a mixed OLAP/OLTP workload. The queries are quite ad-hoc and are dynamically created in the mid-tier application server. When we start the server, the performance is quite ...
2
votes
4answers
904 views
How to move data from big table
There is a big table (5-6 million records). I have to move 90% of old records to other database(table). Solution?
13
votes
2answers
327 views
Setting up a central CLR stored procedure / function respository library for internal stored procs in other databases to use?
I would like to use code that I developed in C# CLR to be used in all of the databases on the system so that I don't have to set each to trustworthy and turn CLR on and keep a bunch of the same code ...
5
votes
4answers
2k views
How to revive SQL Server 2008R2 performance counters?
I'm running SQL Server 2008R2 Developer on W7 x64, as the default instance. For some reason, SQL Server's performance counters seem to have disappeared. SELECT * FROM sys.dm_os_performance_counters ...
4
votes
3answers
4k views
SQL Server 2008 R2: Problems after computer name change
I'm having a confusing problem after changing the computer name of a remote server hosting a local SQL Server instance.
Basically, a remote server was moved from one site to another. In order to ...
4
votes
2answers
2k views
Microsoft SQL Server 2008 Memory
I am running MSSQL 2008 Enterprise and in the configuration of the server the Max memory amount is set to 2147483647 MB.
My Question is, the server has 192 GB of ram,the number in the config can't be ...
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
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 ...
2
votes
2answers
293 views
Performance gain by moving a large table from a database and referencing with a synonym
We have SQL Server 2008 R2 database which is currently about 80Gb.
About 10Gb of this is down to one table, and I wondered if there would be a performance gain if I moved this table into it's own ...
1
vote
1answer
361 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 ...
23
votes
3answers
2k views
Unexpected scans during delete operation using WHERE IN
I've got a query like the following:
DELETE FROM tblFEStatsBrowsers WHERE BrowserID NOT IN (
SELECT DISTINCT BrowserID FROM tblFEStatsPaperHits WITH (NOLOCK) WHERE BrowserID IS NOT NULL
)
...
9
votes
7answers
632 views
Version control for database objects
The database our developers are working on is too large (have a lot of database objects). We have to control db objects changes (change management). Our company cannot have a person who would be ...
18
votes
5answers
3k views
Why is there still a varchar data type?
Many of my databases have fields defined as varchars. This hasn't been much of problem since I live and work in America (where the only language that exists is "American". ahem)
After working with ...
11
votes
3answers
803 views
How often to backup the master database?
The suggestion in BOL is fairly vague:
Back up master as often as necessary to protect the data sufficiently
for your business needs. We recommend a regular backup schedule, which
you can ...
8
votes
2answers
493 views
Optimization: Moving variable declarations to the top of your procedure
While working on optimizing some stored procedures, I sat down with the DBA and went through some sprocs with high blocking and/or high read/write activity.
One thing the DBA mentioned was I should ...
7
votes
3answers
901 views
SQL Server database synchronization
Problem definition
Our users need the ability to query a database that is mostly up to date. The data can be stale up to 24 hours and that is acceptable. What would be the lowest cost approach to ...
6
votes
4answers
4k views
SQL Server database on an SSD - any advantage to a separate file for every table?
I'm creating a database in which there will be around 30 tables, with every table containing tens of millions of rows and each table containing a single important column and a primary/foreign key ...
3
votes
2answers
638 views
TdsInputBufferError not 0 in LoginTimers errors of RING_BUFFER_CONNECTIVITY of sys.dm_os_ring_buffers
I've got an issue with SQL Server.
The value of TdsInputBufferError is not 0 in LoginTimers errors of RING_BUFFER_CONNECTIVITY of sys.dm_os_ring_buffers.
This happens randomly, once or twice a week, ...
2
votes
2answers
3k views
SQL 2008 R2 creates user/schema when Windows user creates tables
We added a server login and database user that map a Windows Group to a SQL 2008 R2 instance using the following script, with the names changed for anonymity:
USE master
go
CREATE LOGIN ...
8
votes
1answer
156 views
Should I be alarmed by this NO JOIN PREDICATE warning?
I'm troubleshooting the bits and pieces of a poorly-performing stored procedure. This section of the procedure is throwing a NO JOIN PREDICATE warning
select
method =
case ...
6
votes
2answers
127 views
How should I configure these disks on a SQL Server for a BI configuration?
Assuming constant memory (32gb) and CPU (4), 2 x disk arrays, I have the following disks
2 x 150 (10k)
6 x 150 (15k)
They are all local disks.
My requirements
My DB is 350gb and set to default ...
4
votes
1answer
131 views
Indexes file damaged, was on ramdisk
I have put some of my indexes into a filegroup that contains one file, that file is on the ramdisk, the performance is great, but the problem is that the file was deleted incorrectly (the file ...
3
votes
3answers
2k views
How? SQL Cluster with multiple active nodes (not failover)
I'm building a new SQL 2008 R2 (Enterprise) Cluster that is backed by an EqualLogic iSCSI SAN. Instead of building a failover cluster (which I have done), I would instead like to setup several nodes ...
0
votes
0answers
79 views
SQL Server connection failed for domain user from Mac OS machine
I have SQL Server 2008 R2 SP1. I am using connection string to connect to SQL Server.
In connection string I am passing the driver path, server name, TCP port number, user name with which I want to ...
9
votes
1answer
2k views
Stored Procedure to return dynamically created table data
Quick back story, we are working with an outside vendor that has a survey system. The system is not necessarily designed the best in that when you create a new survey and the system creates a new ...
7
votes
1answer
709 views
Interpreting SQL Server's Showplan XML
I just rolled out a feature on my site http://sqlfiddle.com that allows users to view the raw execution plans for their queries. In the case of PostgreSQL, MySQL, and (to some extent) Oracle, looking ...
6
votes
2answers
553 views
Reindex Task Failing Randomly
We use the built-in maintenance plan to reindex (and backup, etc.) our production SQL Server. The problem we are currently having is the reindex task started failing randomly this week after months ...
5
votes
2answers
439 views
How export a sql server 2008 diagram to PDF filetype?
I want to have a export from my database diagram to PDF or image types.How can I do this?
I worked with sql server 2008 R2.
5
votes
2answers
110 views
Most effective way to determine the minimum set of permissions for user
I have a database that an application connects to using db_owner permissions.
How do I effectively determine the minimum set of requirements actually needed by this user (application) to run without ...
5
votes
3answers
261 views
Save Connections in SSMS?
Can I save some sort of session or profile in SSMS (2008 r2) that will open up connections to a saved list of database servers when I open SSMS?
So for example I frequently will open up SSMS and ...
4
votes
2answers
98 views
Generate script to automate renaming of default constraints
Background: Some of our default column constraints were generated without explicit names, so we get fun names that vary from server to server like: DF__User__TimeZoneIn__5C4D869D
I would prefer to ...
4
votes
1answer
101 views
Security Question involving Sys tables
We have an application user which has been given read write to the appropriate databases. This user has the ability to query the sys tables by default. I've also heard that there are times when this ...
4
votes
4answers
610 views
Automatically Execute Stored Procedure After Any RESTORE DATABASE Event
Is it possible to have SQL Server 2008 R2 Standard automatically execute a stored procedure in any given database that is restored or attachced to the instance?
I've got close to a solution by ...
4
votes
1answer
540 views
How can dm_db_index_usage_stats show 0 for user_seeks and user scans but a large number for user_lookups?
I'm probably not understanding something but here is the question.
I have done a query on dm_db_index_usage_stats. I've noticed that a couple of my indexes have large numbers (6.5 million and 20 ...
4
votes
3answers
659 views
How can I make my SSIS process consume more resources and run faster?
I have a daily ETL process in SSIS that builds my warehouse so we can provide day-over-day reports.
I have two servers - one for SSIS and the other for the SQL Server Database. The SSIS server ...
4
votes
1answer
3k views
Migrating from one SQL Server 2008 R2 instance to another
I just started a new dba job and have been given a new server and told to move everything from the old server to the new one, brand new windows and SQL Server installations. I know I have to transfer ...
4
votes
1answer
183 views
Migrating large database
What is a quick way of migrating 75G database to the cluster? I tried to test the backup and restore and it took more than 2 hrs. Business owners won't accept the long outage. Any idea how I can make ...
3
votes
3answers
104 views
Password reset for database for VCenter Install
I'm trying to reinstall vCenter SSO and it is prompting me for the "sa" password. I set it up previously, but cannot remember what I used. I uninstalled SSO (as well as the vCenter Server, and ...
