Tag Info

New answers tagged

0

You're uses have long running transactions. They need more drive space for the transaction logs. End of story.


4

Since your databases are in simple recovery mode checkpoint will be taken care of by database engine either automatically or when log file grow 70 % of its size, you can also give manual checkpoint . But still a long running transaction can hold log hostage and would not allow it to truncate even if you manually give checkpoint. Log backup would not be ...


2

I would definitely err on the side of keeping the tables in the same database. It makes it easier to maintain referential integrity (unless you hard delete records in which case foreign keys won't work for you anyway) and create consistent backups (simply backup one database and you know it is in a consistent state, instead of taking two+ backups that my be ...


-1

I would do this in the same database because the data belongs together. To create a trigger for this use, it is not practical to insert records to another database. Refer to the following answer on how to design the auditing table. http://stackoverflow.com/questions/7975317/audit-tables-each-field-for-table-or-one-table


0

Hekaton is Microsoft's copy of the VoltDB architecture from Michael Stonebraker. It is a more radical concept than Oracle's In-Memory seems to be. It involves a sharded, shared-nothing architecture where stored prodcedures are isolated and run to completion without any of the buffering and other overhead associated with transaction management - or what ...


2

You can use Excel to save the file as a .CSV (comma-separated-values), and use SQL Server Management Studio to import the file either into an existing table, or potentially a new table. In SQL Server Management Studio, you select the desired target database, right-click the database name, then click 'Import Data', and go through the wizard step-by-step.


0

I am not a expert in Hekaton but as per Kalens article Yes it does but in different way from Oracle. For processing OLTP data, there are two types of specialized engines. The first are main-memory databases. Oracle has TimesTen, IBM has SolidDB and there are many others that primarily target the embedded DB space. The second are applications caches or ...


1

As you found out, using TOP doesn't work here, because that applies to the entire result set, whereas you want the top N rows from each grouping. One way to do this is by using a ranking function; I've used ROW_NUMBER in this example (note: not syntax checked): SELECT a.EmpName, a.Activities, a.Date FROM ( SELECT ...


1

Yes it is possible... I want to call out a few points before offering the solution: You must configure permissions so that the appropriate applications have the full control over the directory and files in the destination. This includes: SQL Server Service Account or whichever account you are running the SQL Server process under. If batch jobs run under ...


0

No there are no specific bad things about using temp tables and temp procedures. For the temp tables you'll want to make sure that you've got indexes as needed when querying from them, but that applies to normal tables as well.


3

You should focus on breaking your ETL process in chunks to manage transaction log growth. There are other options like setting recovery model to bulk log selective transactions but with bulk logged recovery model you loose point in time recovery. And I do agree setting log file on remote storage is not a suggested practice and it will make operations slow.


1

VLF are totally managed internally by SQL server, I am talking about there size. If you have large amount of VLF's for the database its not a good sign.I guess you are referring to large amount of VLF as VLF fragmentation. High amount of VLF can be attributed to fact that autogrowth settings are not proper for database may be very small and database has to ...


1

I doubt that the example you provide is a valid use case for temporary procedures (I don't see any benefit here to using #temp procedures over permanent procedures), but for #temp tables, which have a much wider set of use cases, the only way to fight these arguments with policy-setters is to run the code - using a full load and during typical workload ...


0

Another way, that can be easily extended to more than 3 columns: SELECT a, b, c, Equal = ( SELECT r = CASE WHEN MIN(v) = MAX(v) THEN 'Y' -- all non-null values equal WHEN MIN(v) < MAX(v) THEN 'N' -- at least 2 non equal ELSE 'Y' -- all nulls ...


0

You were almost there with your query. However, your query fails, when validating isnull(A,B) = B, when B is null, since NULL does not equal NULL in sql. try this: declare @t table ( A decimal,B decimal, C decimal ) insert Into @t(A,B,C) values (1,1,1) insert Into @t(A,B) values (2,2) insert Into @t(A,C) values (2,2) insert Into @t(A,C) values ...


-5

This COALESCE() function is only available in version of sql server greater than 2012.


0

We had to bring MS in on this one via a paid support call. After a week of dumps, xperf analysis, driver updates, playig with Virtual infrastructure we ruled out all the basics (or so we thought) - over a week later we found the problem - there was over 500,000 sys.server_event_notifications defined - these all looked as follows: name object_id ...


2

I have had great success with overall perfomance gains by moving the clustered index to an identity, and leaving the GUID as the PK. If you have a GUID as a clustered PK, you are telling SQL to physically sort the data randomly, leading to page splits and fragmentation in the main table as well as in the non-clustered indexes. By getting your database on ...


2

Assuming you want all NULLs to count as equal, I think this will work. But I haven't thoroughly tested it, or even thought about it for very long... select A,B,C,'Equal' = case when COALESCE(A,B,C,0) = COALESCE(B,C,A,0) and COALESCE(B,C,A,0) = COALESCE(C,A,B,0) then 'Y' else '' end from @t COALESCE will pick the first non-NULL value ...


3

The below sample structure illustrates how you can do the TasksTags table most efficiently. The Tasks table enforces unique task names. The Tags table enforces unique tag names. The TasksTags table joins these together allowing any combination of Tasks and Tags. USE tempdb; CREATE TABLE dbo.Tasks ( TaskID INT NOT NULL CONSTRAINT PK_Tasks PRIMARY KEY ...


0

@Kin yes he wants just to remove the partition so it becomes non- partitioned table. as Aaron said I would select * into new_table from old_paritioned and rename the new table and drop the partitioned table.I am sure they are better ways of doing that though. If it is large table(billion of rows) copying all rows takes time and resources.


1

The TOP does nothing - based on the fact that it's "100%". The first query is essentially the same as: WHERE (ds.DocumentSourceName NOT LIKE 'ZZ%'), but the second has more filters...


0

Depending on what the structure of the field 'name' is, you could use: SELECT name,number2 FROM [Crm7.contact] WHERE name = 'YourNameYouWantToFind'


2

The Degree of Parallelism event actually records the query parallelism in the BinaryData column, not the EventSubClass. See here: 0x00000000, indicates a serial plan running in serial. 0x01000000, indicates a parallel plan running in serial. >= 0x02000000 indicates a parallel plan running in parallel. From my simple tests, RML or trace replay do not ...


0

USE master GO CREATE SERVER AUDIT BackupTrap TO APPLICATION_LOG WITH (QUEUE_DELAY = 0, ON_FAILURE = CONTINUE) GO --Turn the Audit On ALTER SERVER AUDIT BackupTrap WITH (STATE = ON) GO --Create the Database Audit Specification USE AdventureWorks2012 GO CREATE DATABASE AUDIT SPECIFICATION BackupTrapAdventureWorks FOR SERVER AUDIT BackupTrap ADD ...


1

I know this question has been already answered, but I found an article that easily details all of the answers above with screenshots included. It also has some more details related to SQL Server compression, including some recommendations and some guidelines for security in order to use the T-SQL Script above to change the default compression. You can ...


2

What you can do is disable the "sa" login However, I suggest you check who has the "sa" login privileges and whether these are the right persons. A monitoring tool can be an answer, as it will show you everything that the "sa" login did on your instance.


2

Check if sql server service account has full permission on the drive / folder where you copied the mdf file. (Right Click on the drive / folder >> Properties >> Security)


1

You're correct about how the VLFs grow in size. Check out the following video from Jes Borland for some more tidbits. How SQL Server Works: Log File (Video)


1

I would not get hung up on number of VLF's, especially with 160 VLFs. if the VLFs are in 1000's then i'll put it on the list of things to tackle as it can be painful in a 24x7 environment. The fragmentation of your db log shouldn't be an issue as its sequential. if its been growing in very small chunks, then waiting for a maintenance window, backup the log ...


3

There is no way to prevent the "sa" login from accessing every database on the server. If the "sa" login isn't needed (which it shouldn't ever be needed) then disable it. If it is needed, then there's nothing you can do as anything that you put in place to block it, the "sa" login can remove.


4

First, are you sure the data growth won't happen again? If there's a realistic chance it will and the empty space doesn't hurt you, leave it, do NOT shrink the database. However, if you're positive you want to reduce the data file size, then you should be aware of the pitfalls of data file shrinking: It causes massive index fragmentation. Affects ...


1

I recommend you use DBCC SHRINKFILE(). You can check the available space available by file using the query in the answer at this link How to determine used/free space within SQL database files? If you only have one data file and one log file it'll probably just be file #1 and file #2. You have to pass the file number to the SHRINKFILE command. It's not ...


1

First, make sure you have a user in msdb associated with their login. USE msdb; GO CREATE USER username FROM LOGIN loginname; Next, deny execute on sp_help_job and sp_start_job for that user. USE msdb; GO DENY EXECUTE ON dbo.sp_help_job TO [username]; DENY EXECUTE ON dbo.sp_start_job TO [username]; Next, create two wrapper stored procedures that ...


0

I can't test it because I don't have Express installed here, but can't you use Profiler on SSMS 2012 on an express edition? SSMS 2012 is available to download here free, I can't think of any reason why anyone using SQL Server shouldn't be using it. Can someone with an Express Edition installed verify?


0

If looking for a UI that will get traces in real-time, the ExpressProfiler is good. But it's a bit simple, and not very configurable. This is a nice alternative, very configurable and I use use it all the time now. It's called Sql ExpressTRace and this link is to an article that lets you download the compiled UI and the code too: ...


0

The problem was indeed a corrupted backup, as Kin correctly deduced. Most likely the corruption was introduced during an ftp transfer. So, when you get the error "The media family on device 'C:\myfile.bak' was created using Microsoft Tape Format version 1.8. SQL Server supports version 1.0." or the error "Specified cast is not valid. (SqlManagerUI)" when ...


1

The trick is to avoid the orphaned logins in the first place, not to synchronize them afterwards. As you probably know, 'orphaned' users are due to the SID associated with the user in the database not matching the SID associated with the login on the server. When a login is created on a server, the value for the SID is either taken from Active Directory ...


0

I highly suggest that you partition your cube. Then the server can parallelize the processing of the fact table, which likely will give you the biggest performance gain.


4

I think you could use a DDL trigger if you could build a reliable LIKE expression that determines that a column has been specified with a default but does not contain the word constraint before the word default. But that won't be very fun. If you don't have PBM you're probably better off just manually reviewing the DDL in the default trace or having a DDL ...


4

That is not a supported upgrade path. how would you recommend I proceed? My recommendation would be to install side-by-side and then migrate (backup and restore) your databases to the SQL Server 2014 instance.


1

from your question i understand that you have a "One to many" relationship between The Division to the Regions, the best way with this kind of relational data from my experience is to create a Division table and a Region table that holds the DivisionID. that will also use less storage space because you won't need to write the division name in string, you ...


4

It sounds like this is more of a DataWarehouse workload than an OLTP. In that case, heavily indexed tables are normal. Keep in mind a couple of things, when you do your nightly loads, drop the indexes first then reindex after. Also, there will be a higher storage cost but generally this wont be a problem (but be aware of it). I know your using 2008R2 but ...


2

You need to use: DENSE_RANK() OVER (ORDER BY CustomerID, Value) Test at SQL-Fiddle


1

I am sorry to say that this does not look like a good implementation of dbo.Eval. Since this script uses xp_cmdshell and osql, it means that for every call the xp_cmdshell command shells out to the operating system, executes the OSQL command line utility (which has been replaced by the newer SQLCMD command line utility) and inserts the answer into the ...


1

Unless I'm missing a key point here, I don't understand why you would go to so much trouble to evaluate an expression. Having said that, you certainly shouldn't do anything in master, and if you must, why not just do something like: CREATE PROCEDURE Eval ( @Expression AS NVARCHAR(MAX) ) AS BEGIN DECLARE @cmd NVARCHAR(MAX); SET @cmd = 'SELECT ' ...


3

Yes, DTutil is still going to be the best option. I have a post, SSIS Package Extract from MSDB on how to use dtutil + the data in the packages table to generate the call(s) to export to disk. Instead of the file destination, you would substitute sourceserver. If you've used folders to organize your packages, then you'll need to create them beforehand. ...


1

A colleague of mine had a similar-ish problem with long-running shrinks. He took some time to investigate it and found that he had lots of indexes at the 'end' of the file. A little index maintenance, and then the shrink operation ran fine. You can read his blog about it here: http://sqltrees.wordpress.com/2013/08/11/shrinking-databases/


0

Now I would like to audit those connections - when they are happening and to which database. If you want to audit on an ongoing basis then a server side trace will do the job. Below is how you will script out from Profiler Entire SCRIPT : /****************************************************/ /* Created by: SQL Server 2014 Profiler */ ...


1

In order to get the application pool account to be recognized by the SQL Server you'll have to configure the application pool to run under a domain account. Then give this account the rights that it needs within the SQL Server instance. If you are using Windows auth then your users will need read access to the folder which is hosting the website, otherwise ...



Top 50 recent answers are included