Basically, what I'm doing is storing answers to questions in a survey. I have two ways I can organize the table:
1. Just having a table with lots of columns - one for each question
2. A table with only about maybe 3 columns:
SurveyID
QuestionID
QuestionAnswer
In this second case, the the primary key would be both SurveyID and QuestionID combined, of course.
I don't fully know the pros and cons of the two approaches, and both look like they would work. Right now, I'm using option 1, but I keep wondering if option 2 might be better. Whenever I change the questions, I currently have to drop and recreate the table (altering it is too much effort), and I know option 2 would be a way of avoiding that. By the way, the questions themselves are stored in an xml file, if it means anything. Anyhow, once the survey is being used, there shouldn't be any further changing of questions. And there's also just too much I don't know about (how is performance affected, for example?).
I need to reduce the size of a db from it's original allocated size of 2.0 gb to 1.0 gb.so that I can allocate more to another db.How can I do?Thanks, Ravi.
I have a search function, which searches across many tables.
It's a pretty heavy SPROC, I'm wondering in general, what are the best way to reduce deadlocks ? Its used a fair bit, and altho I haven't noticed problems with it myself, there are definately a decent amount of deadlocks showing up in the logfiles.
I've always assumed this is something really difficult, and avoided it like the plague.
I'm new to SQL Server Maintainance. I need some disk space now. In my database, I have some table that has 7 year old data and I can get rid of that without any conequences. Could you please tell me the best course of action ?? I was thinking about:
1. Dropping those tables. 2. Recreating them again 3. Running ShrinkDB on entire DB.
However, this solution does not sound right or elegant to me. Could you please help me with that ? Thanks
I am running MS SQL 2005 Express I get per day 2-4 hackers attacks trying to login from €œsa€? Some 37 calls times per second one of attack was continuing 4 days
Is there some setting into MS SQL 2005 to reduce that?
Can you recommend me good firewall for DDOS attacks?
Is it there some legal action that I can take to this people I have their IPs most are from US and Canada?
I'm worhing with SQL Server7.0 and I have created a database. I have objets and data in my database My database have 300Mb and I only need about 50. How can I reduce to 50 the dimension of my database. Is it posible?? Any advice will be greatly apreciatted.
My database's transaction log has become 1.7 GB. Can I reduce it's size? I have tried to shrink database and also set truncate on checkpoint option and also taken the backup after that. but nothing helps. Please advice.
I am using SQL 7, SP1 / NT 4. The .LDF file has grown to 1.1GIG; I ran a DBCC SQLPerf(LogSpace), the used portion of the log is 2%. When I run a DBCC Shrinkdatabase and DBCC Shrinkfile, the log file does not reduce in size. How do I get the virtual log files that are not active released back to the system? Is there a way to tell if all the virtual log files are active, therefore, not reducing the size of the file? Any help is greatly appreciated.......
I had a database that’s comprised of different file groups and log files spread out among different hard drives. I have recently upgraded the database to SQL 7.0 on a RAID 10 volume. I would like to consolidate all the file groups and files as well as various log files into one primary datafile and logfile. How do I do that? Thanks in advance.
Can anyone suggest a method to reduce the size of one our log devices. The DB was set up initially at 500Mb with a log size of 1 Gb (typo by the client). We would like to reduce it to 100Mb. if possible. Our environment is SQL Server 6.5 with service pack 3.
I am running Slq2000 (EV) on NT4.0. I have problem is that a size of Transactional Log(*.ldf) file is 3GB. I want to reduced the size to 2GB.Can anyone help me answer this question.
Note: I hv already took the backup of Transactional file by choosing Trunct file after backup but size is not got reduced.
IF (EXISTS (SELECT name FROM sysobjects WHERE (name = N'Fn_Get_Consensus_Curve_41_Data') AND ((type = 'P') OR (type = 'IF') OR (type = 'TF') OR (type = 'FN')))) DROP FUNCTION [dbo].Fn_Get_Consensus_Curve_41_Data
GO
*/ declare @p_ENTITYID INT declare @p_CUSTOMERID INT
Declare @p_Login_Type int Declare @p_Result_Status int set @p_Login_Type = (SELECT DBO.GET_USER_LOGIN_TYPE_ID(@p_UserID))
If @p_Login_Type=1 and not (@p_CustId is null or @p_CustId='') Set @p_Result_Status = 1 Else if @p_Login_Type > 1 Set @p_Result_Status = 2 Else Set @p_Result_Status = 0
If @p_Result_Status > 0 -- if user is valid and given enough parameters than Begin If @p_Result_Status = 1 -- if User is trader and gives customer id Begin Declare Cur_Fetch_Curve_Cust_Data cursor for Select Distinct Customerid From PricesRR PRR Where Convert(Nvarchar,Matchdate,101) = Convert(Nvarchar,@p_Match_Date,101) And Sector_Id = @p_Sector_Id And Location_Code = @p_Location_Code And CustomerID = @p_CustId And --CustomerID <> 0 --CustomerID not in (0, -1, -2, -3, -100, -200) CustomerId Not In (Select CustomerId From Fn_Get_PricesRR_Not_To_Include_Cust_Id('V')) and isnull(PRR.Record_Last_Action,'N') <> 'D' and Version = dbo.GET_PRICESRR_MAX_VERSION(@p_Location_Code, @p_Sector_Id, @p_Match_Date, PRR.EntityID, @p_CustId, PRR.Date)
Declare Cur_Fetch_Curve_Entity_Data cursor for Select Distinct EntityID From PricesRR PRR Where Convert(Nvarchar,Matchdate,101) = Convert(Nvarchar,@p_Match_Date,101) And Sector_Id = @p_Sector_Id And Location_Code = @p_Location_Code AND EntityId IN ( Select Distinct Entity_Id from Fn_Get_Allowed_Entity_List(@p_Location_Code , @p_Sector_Id , @p_Match_Date ,@p_UserID )) and isnull(PRR.Record_Last_Action,'N') <> 'D' and Version = dbo.GET_PRICESRR_MAX_VERSION(@p_Location_Code, @p_Sector_Id, @p_Match_Date, PRR.EntityID, @p_CustId, PRR.Date)
End Else If @p_Result_Status = 2 -- if User is higher than trader.. means broker or higher Begin Declare Cur_Fetch_Curve_Cust_Data cursor for Select Distinct Customerid From PricesRR PRR Where Convert(Nvarchar,Matchdate,101) = Convert(Nvarchar,@p_Match_Date,101) And Sector_Id = @p_Sector_Id And Location_Code = @p_Location_Code And --CustomerID <> 0 --CustomerID not in (0, -1, -2, -3, -100, -200) CustomerId Not In (Select CustomerId From Fn_Get_PricesRR_Not_To_Include_Cust_Id('V')) and isnull(PRR.Record_Last_Action,'N') <> 'D' --and Version = dbo.GET_PRICESRR_MAX_VERSION(@p_Location_Code, @p_Sector_Id, @p_Match_Date, PRR.EntityID, @p_CustId, PRR.Date)
Declare Cur_Fetch_Curve_Entity_Data cursor for Select Distinct EntityID From PricesRR PRR Where Convert(Nvarchar,Matchdate,101) = Convert(Nvarchar,@p_Match_Date,101) And Sector_Id = @p_Sector_Id And Location_Code = @p_Location_Code and isnull(PRR.Record_Last_Action,'N') <> 'D' --and Version = dbo.GET_PRICESRR_MAX_VERSION(@p_Location_Code, @p_Sector_Id, @p_Match_Date, PRR.EntityID, @p_CustId, PRR.Date)
End delete from @Temp_Curve_Submission_Data
----------------------- -----------------------
Open Cur_Fetch_Curve_Cust_Data fetch next from Cur_Fetch_Curve_Cust_Data into @p_CUSTOMERID WHILE @@FETCH_STATUS = 0 BEGIN
IF @@FETCH_STATUS <> 0 break BEGIN ----------------------- ----------------------- Open Cur_Fetch_Curve_Entity_Data fetch next from Cur_Fetch_Curve_Entity_Data into @p_ENTITYID WHILE @@FETCH_STATUS = 0 BEGIN
On my server C drive is of 34GB. Right now tempdb size is 22GB which is causing C drive to be full. How I can I reduce it? I dont want to move tempdb to any other drive, and I am only looking a way to reduce its size.
i m using SQL 2000, i have a database with 86G mdf and 56G ldf size. i shrink the ldf and it reduced to 32M, however, i did not do anything on my mdf file, but the size of mdf has been reduced to 28G. just would like to check, is this correct?why is mdf size reduced when i only shrink my ldf? hope can help. thanks
Database log of my DB is around 2GB.The database is using FULL recovery option.I want to reduce the file size of the log cause it takes up a lot ofspace.I'd do a full database backup, then backup the transaction log as well.... both backup performed with a check on the option "clear inactiveentries from transaction log".But after I backup, the database log is still 2GB.What should I do to reduce the database log file size?Should I use?:==============================Dump Tran databaseName with no_logDBCC shrinkdatabase(databaseName, 30)==============================Is that safe to be used in production server?Peter CCH
Hi, my database size has grown out of control and I need help with thefollowing issues. (I am very new to databases)I am storing financial tick data in one of the tables and after two monthsthe database has grown to 30GB. I do not need a permanent record of thistick data after it has been processed and tried to remove all rows from thistable (delete from Tickdata), however sql does not take kindly to removingmillions of rows and the operation seams to time out. The only solution Icould come up with was to delete the table.Secondly, after managing to clear out these tables I have noticed that thedatabase size is still 30GB, despite 29GB being available. Is there any wayto reduce the size of the database from 30GB. I tried the shrink databaseoption but it does not do anything. Any ideas?Thanks.
I have a table with column value like '123 345 678 143 648' like that. What I need to do is I have to take each code value and put it as a new record in another table. So, if I say 'Select substring(column_name,1,3) from table' then it is very fast (fraction of second). But since I need to take each code and the # of codes in each record may vary, I am using a while loop to take each code and so I delclared a variable @i and now my select statement is like this: 'Select substring(column_name,@i,3) from table'. Interesting now this select statement is taking almost 2 mins for each iteration.
Why it is like this? Is there any way I can reduce the time taken to execute each iteration?
I want to write a quick one time query to create a new table based off an existing table. The idea is to make the new table more efficient by reducing the amount of records...see example below
id1 id2 country 1 2 US 3 4 AU 5 6 US 7 8 US 9 10 PE 11 12 PE 13 14 US 15 16 US 17 18 US 19 20 US 21 22 US
id1 id2 country 1 2 US 3 4 AU 5 8 US 9 12 PE 13 22 US
Case: Exporting Report to PDF/Printing/TIFF Report: Contains 1 table with 19 Columns. 1 column is static, the other 18 are visible at the users descretion. Report when printed/exported to pdf spans 2 pages naturally, 16 on the first page, 3 on the second, and the column widths have been adjusted to provide a perfect page span .
User A elects to hide two of the columns, and show the rest. The report complies and the viewable version is perfect, the excel export is perfect.. the PDF export on the first page causes every fith column, starting with the last column that was hidden to be expanded to take up additional width. On the spanned page, it renders the first column on that page correctly, then there is a white space gap equal to the width of the hidden columns and then the rest of the cells show with the last column expanded to take up the same width that the original 2 columns were going to take up, plus its width.
We have tried several different settings to see if it helps this issue or makes it worse. So far cangrow/canshrink/keep together have made no impact. It is not possible to increase the page size due to limited page size selection availablility for the client. There are far too many combinations of what the user can elect to show or hide to put together different tables to show and hide on the same report to remove this effect.
Any help or suggestion on this issue would be appreciated
from this code, i want to reduce product's quantity in database. when I click buy button,it reduce old quantity and new quantity (quantity from cart when I want to buy it) in auto. please advise me.
hi, i'm a newbie in SQL Server and have recently setup a test SQL 7 server. I used all the defaults at the beginning, and now the MDB file is about 500MB and the LDF file is of similar size.
i'm still trying to figure out how to reduce the size of the transaction log file. Currently I only have full backup of the database once a week, and there is no backup for transaction log.
as of this moment, the transaction log is of not much use to me, but I really want to get it reduced as i'm running out of disk space.
and i'd also greatly appreciate if someone could suggest a good DBA practice on the proper setup/handling of transaction logs (how to balance the disk-space usage AND be able to use the transacton logs for proper roll-over during a recovery process).
i'd soon be setting up a SQL 7 server where about 10 active users are expected at any one time. I've read that the transaction log file should be about 40% to 50% of the estimated size of the database file, and should be allowed "auto-grow". So what happens if the more space is required by the transaction logs? Does a full-backup purge the transaction logs (like the way they do in Exchange Server)?
The size of my transaction log file is out of control. I've backed up the database and the transaction log went from 120 GB to 120 MB. Now, I can't reduce the size of the transaction log file. It's still at 120 GB (w/ almost all of that being held as Free Space). I get errors when I try to manually reduce the file size. Any tips?
Hi,I have a task at hand to reduce the time taken for search query toexecute. The query fetches records which will have to sorted bydegrees away from the logged in user. I have a function whichcalculates the degrees, but using this in the search query slows theexecution and takes about 10 secs to complete which is unacceptable.Please advice. Your help is much appreciatedFor more details plz see:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=97021ThanksIsfaar
Hello,I am hoping you can help me with the following problem; I need to processthe following steps every couple of hours in order to keep our Sql 2000database a small as possible (the transaction log is 5x bigger than the db).1.back-up the entire database2.truncate the log3.shrink the log4.back-up once again.As you may have determined, I am relatively new to managing a sql serverdatabase and while I have found multiple articles online about the topics Ineed to accomplish, I cannot find any actual examples that explain where Iinput the coded used to accomplish the above-mentioned steps. I dounderstand the theory behind the steps I just do not know how to accomplishthem!If you know of a well-documented tutorial (Aside from Books Online (F1)),please point me in the right direction.Regards.
I looked at the number of VLFs in ony databases and figured out it was 300. I looked at the log file and its 40GB with 99% free space. I reduced the log file to 1GB. Now I looked at the VLFs and the count is 250. so 250 vlfs in a 1GB of log file. Is there a way to get the VLFs to a decent number?
Also my transaction log backups run once every 5 mins and auto growth on data and log files is 10MB.
I have patch server on which the database is SQL server 2000.
The Database size is 27GB and I want to reduce the size of DB by deleting all the records and keeping only year 2007 records. Please advise me how to reduce the size of the DB.
If I need to delete any record, please let me know how to delete it. If their is any Query for it, please send the Query.
