Dropping indexes before bulk insert

Hi all,

I have a huge table 170 Gb of size. On that table we have 10 indexes
of around 12 GB in size.

The application is designed such that it bulk inserts the file in to
sql server. But , often we are getting time outs and some latching
isssues ( as can be seen in activity monitor).

So, will this be a good idea of dropping those indexes and then
recreating them again for better performance.

1) Its SQL 2005 Standard Edition SP1

2) Databases are in SIMPLE Recovery mode.

3) Database is not OLTP.

Thanks.

//N

Hi Naj,

Do you see any blocking on the server while the bulk insert is running?

How do you mean latches issues ?

Could you please look for waittime and waitypes in the following view and let us know what is there.

select * from sys.dm_os_waiting_tasks where session_id > 50

Jag

|||

you should be better off by dropping indexes and after bulk insert creating them off line. indexes created offline have very small footprint on log and much faster then indexes created online. as a trade off your table will be offline for a while. how long it depends on clustered key as with large table clustered key plays very vital role when rebuilding indexes.

another point is to consider switching off AUTO_UPDATE_STATISTICS while inserting rows into your table. this will kill the performance while inserting data and updating staticstics at the same time. also there is a new option AUTO_UPDATE_STATISTICS_ASYNC which provide background statistics update. use with extra care as they can hinder performance significatly.

see article http://www.mssqltips.com/tip.asp?tip=1193

Dropdown width for Multi-select parameters

How can I control the width of the dropdown for multi-select
parameters? It seems to be showing up equal width dropdowns
irrespective of the size of the text. The problem crops up when the
length of the text for any option exceeds the default length, I get an
ugly horizontal scroll bar.
I can have a temporary fix by abbreviating the text content, but that
is not a long-term option.
N.On Feb 20, 1:18 pm, "Nidhee Pathak" <nid...@.gmail.com> wrote:
> How can I control the width of the dropdown for multi-select
> parameters? It seems to be showing up equal width dropdowns
> irrespective of the size of the text. The problem crops up when the
> length of the text for any option exceeds the default length, I get an
> ugly horizontal scroll bar.
> I can have a temporary fix by abbreviating the text content, but that
> is not a long-term option.
> N.
As far as I know, there's not a way to control the width of the drop-
down parameter. That said, it might be possible to modify the
underlying XML in the RDL file in some way that has not been readily
documented.
Regards,
Enrique Martinez
Sr. SQL Server Developer|||What you can do is, you must be using a seperate query for displaying? , so
format / pad with spaces for display field from the sql query, so that the
display field can be in the required size, but the value is same.
Amarnath
"Nidhee Pathak" wrote:
> How can I control the width of the dropdown for multi-select
> parameters? It seems to be showing up equal width dropdowns
> irrespective of the size of the text. The problem crops up when the
> length of the text for any option exceeds the default length, I get an
> ugly horizontal scroll bar.
> I can have a temporary fix by abbreviating the text content, but that
> is not a long-term option.
> N.
>

Dropdown list for multi-select does not dynamically size to accomodate the data

I've noticed that the dropdown list for the multi-select parameter does not
dynamically size so that it is not possible to read all the data in the
dropdown lists. It does seem to work with a single-select however. Does
anyone know if this gets fixed with VS2008?No replies? Anyone? I'm having the same issue... If I can find something
I'll post it here.
"Susan" wrote:
> I've noticed that the dropdown list for the multi-select parameter does not
> dynamically size so that it is not possible to read all the data in the
> dropdown lists. It does seem to work with a single-select however. Does
> anyone know if this gets fixed with VS2008?
>
>

Drop large transaction log file and create new

Team,
Could you sned me some idea, how can I remove or replace transaction log file on MS SQL 2000 server? Data size is about 2GB but trx log is more than 35GB. This database include only static data...there are no transactions. I have about 5 million records in one table and there are 13 tables with 40 000-50 000 records.

It's very urgent because we need to clean up space on NT server tonight.

Thanks,
AttilaOriginally posted by horvata
Team,
Could you sned me some idea, how can I remove or replace transaction log file on MS SQL 2000 server? Data size is about 2GB but trx log is more than 35GB. This database include only static data...there are no transactions. I have about 5 million records in one table and there are 13 tables with 40 000-50 000 records.

It's very urgent because we need to clean up space on NT server tonight.

Thanks,
Attila

See: http://dbforums.com/t546372.html|||Thanks a lot for your help.|||Originally posted by DBA
See: http://dbforums.com/t546372.html

Whou much time you spend to make you database full backup ??
Wich type of backup do you do ?

If you log is no longer used (because you data is static) you can use the command bellow

sp_detach_db <dbname>

GO

CREATE DATABASE <dbname>
ON PRIMARY (FILENAME = '<path>.dbname.extension')
FOR ATTACH
GO

Ps: Make 2 full backups of you database before do this. On filename choose the path of you datafile <only>, forget you logfile.

Jorge|||Just for your information...
I created a new database and I exported old objects into new db. After that I dropped old database and I created new database with the original name and then I exported back db objects. Now I have a DB Maitenence Plan which is working fine and there are no issue with log file size.

Thanks,
Attila|||Originally posted by horvata
Just for your information...
I created a new database and I exported old objects into new db. After that I dropped old database and I created new database with the original name and then I exported back db objects. Now I have a DB Maitenence Plan which is working fine and there are no issue with log file size.

Thanks,
Attila

Hi Attila, I discover another way to do this.

Ps:Allways execute a full backup before.

First execute
EXEC sp_detach_db 'database_name', 'true'

Rename your physical log file on Operation system
After this execute the following command.

EXEC sp_attach_single_file_db @.dbname = 'database_name',
@.physname = 'path\database_name.extension'

This command works. I haver already done this.

Jorge Demattos
Bank of America.